SQL query including counts on grouped data-CodePudding

given this data

name      type       date
--------------------------------
john     typeA      2022-06-01 
james    typeA      2022-06-01
james    typeA      2022-06-01 
james    typeB      2022-06-01
bob      typeA      2022-06-01
bob      typeB      2022-06-01

How should I structure a SQL query (SQL Server 2019) to output, without using subqueries?

name    countOfTypeA    countOfTypeB
------------------------------------
john    1               0
james   2               1
bob     1               1

CodePudding user response：

You can achieve by doing like. I have used cte for the query. Here is the query

;with cte ([name],[T1],[T2])
as
(

    SELECT [name], 
           SUM(CASE WHEN [type]='typeA' THEN 1 ELSE 0 END) AS T1,
           SUM(CASE WHEN [type]='typeB' THEN 1 ELSE 0 END) AS T2
    FROM tbl_test
    GROUP BY [type],[name]
    
    )
    select [name],SUM([T1]) as countOfTypeA,SUM([T2]) as countOfTypeB from cte group by [name]

CodePudding user response：

You can use the PIVOT relational operator to do this. See the documentation.

declare @data table ([name] varchar(10), [type] varchar(10), [date] date)

INSERT INTO @data([name],[type],[date]) VALUES 
('john', 'typeA', '2022-06-01'),
('james', 'typeA', '2022-06-01'),
('james', 'typeA', '2022-06-01'),
('james', 'typeB', '2022-06-01'),
('bob', 'typeA', '2022-06-01'),
('bob', 'typeB', '2022-06-01')

SELECT [name], [typeA] AS countOfTypeA, [typeB] AS countOfTypeB
FROM  @data
PIVOT (
  COUNT ([type])  
  FOR [type] IN ([typeA], [typeB]) 
) AS pvt

Result:

name       countOfTypeA countOfTypeB
---------- ------------ ------------
bob        1            1
james      2            1
john       1            0

(3 rows affected)