Q:

Group by in sql for two conditions

I have this table 
SELECT 
      [WeldStatus]
      ,PackageId

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

How can I group by this query to calculate total testpackage and total accept and total remain?
For example for packageid=6030 I need this:
packageid   total   accept remain 
6030        4       3      1

A:

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;

