CODEWITHSUNDEEP
sqlsql-server
TyForHelpDude
TyForHelpDude

Reputation: 5001

Group by datepart and find total count of individual values of each record

This is table structure;

ID  Score  Valid    CreatedDate
1   A      1        2018-02-19 23:33:10.297
2   C      0        2018-02-19 23:32:40.700
3   B      1        2018-02-19 23:32:30.247
4   A      1        2018-02-19 23:31:37.153
5   B      0        2018-02-19 23:25:08.667
...

I need to find total number of each score and valid in each month

I mean final result should be like 

Month    A     B     C     D   E   Valid(1) NotValid(0) 
January  123   343   1021  98  12  1287     480
February 516   421   321   441 421 987      672   
...

This is what I tried;

SELECT DATEPART(year, CreatedDate) as Ay,
(select count(*) from TableResults where Score='A') as 'A',
(select count(*) from TableResults where Score='B') as 'B',
...
  FROM TableResults 
  group by DATEPART(MONTH, CreatedDate)

but couldn't figure how to calculate all occurrence of scores on each month.

Upvotes: 1

Views: 51

Answers (1)

xQbert
xQbert

Reputation: 35333

Use conditional aggregation.

SELECT DATEPART(year, CreatedDate) as YR
, DATEPART(month, CreatedDate) MO
, sum(Case when score = 'A' then 1 else 0 end) as A
, sum(Case when score = 'B' then 1 else 0 end) as B
, sum(Case when score = 'C' then 1 else 0 end) as C
, sum(Case when score = 'D' then 1 else 0 end) as D
, sum(Case when score = 'E' then 1 else 0 end) as E
, sum(case when valid = 1 then 1 else 0 end) as Valid
, sum(case when valid = 0 then 1 else 0 end) as NotValid
FROM TableResults 
GROUP BY DATEPART(MONTH, CreatedDate), DATEPART(year, CreatedDate)

I'm not a big fan of queries in the select; I find they tend to cause performance problems in the long run. Since we're aggregating here I just applied the conditional logic to all the columns.

Upvotes: 4

Related Questions