Ehsan Akbar Ehsan Akbar - 3 months ago 8
SQL Question

Group by in sql for two conditions

I have this table

SELECT
[WeldStatus]
,PackageId

FROM [SPMS2].[dbo].[JointHistory]


enter image description here

How can i group by this query to calculate
total testpackage
and
total accept
and
total remain


For example for packageid=6030 i i need this :

packageid total accept remain
6030 4 3 1

Answer

Use CASE and Group By

SELECT 
      PackageId,
      count(*) as total,
      sum(case [WeldStatus] when 'Accept' then 1 end) as accept,
      sum(case [WeldStatus] when 'accept' then 0 else 1 end) as remain
  FROM [SPMS2].[dbo].[JointHistory]
group by PackageId;
Comments