2

I have the following Result from Select statement

UnitId     UnitType  GroupId
 1          1          1
 2          1          1
 3          1          2
 4          2          2
 5          2          2
 6          2          2
 7          2          2

I need the following result for each group Id

GroupId CountBasedOnUnitType1 CountBasedOnUnitType2
   1           2                      0
   2           1                      4

Thanks in advance.

Syed Fasih
  • 302
  • 4
  • 12

2 Answers2

2

Try this

SELECT * FROM
(
  SELECT GroupId,
    UnitType
  FROM Table1
) x
Pivot
(
  Count(UnitType)
  For UnitType in ([1], [2])
) p

Fiddle Demo

Output

GroupId        1          2
   1           2          0
   2           1          4
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • Is there any other way without declaring column names? What happens if there are houndreds of Groups? – GorkemHalulu Jun 23 '14 at 15:07
  • Dynamic usage can be seen here. http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server – Jaaz Cole Jun 23 '14 at 19:15
0

Does in necessarily need to have OVER?

select 
  GroupID, 
  sum(case when UnitType = 1 then 1 else 0 end) CountBasedOnUnitType1, 
  sum(case when UnitType = 2 then 1 else 0 end) CountBasedOnUnitType2
from table
group by GroupID
Maxim Balaganskiy
  • 1,524
  • 13
  • 25
  • I have achieved this usign OVER Clause as : SELECT gu.GroupId ,Count(CASE gu.UnitType WHEN 1 THEN 1 ELSE NULL END) OVER (PARTITION BY gu.GroupId) AS CountBasedOnUnitType1 ,Count(CASE gu.UnitType WHEN 2 THEN 2 ELSE NULL END) OVER (PARTITION BY gu.GroupId) AS CountBasedOnUnitType2 FROM Group_Unit gu – Syed Fasih Jun 23 '14 at 08:09