CODEWITHSUNDEEP
sqlsql-server
PigsIncorporated
PigsIncorporated

Reputation: 153

SQL query (Count)

This query is perplexing me.

I have a table with a few columns. Two of the columns are 'Month' and 'Year' where month would be 1, 2, 3, through 12 and Year would be 2016, 2015, 2014, etc.

I need to create a query where it will return the total count of all records for the year by quarter. So 'If Month = 1, 2, 3' then Quarter is 1' and do this per year.

Ideally I want a query returned with the records like:

Year, YearCount, Q1Count, Q2Count, Q3Count and Q4Count

Make sense? Any ideas would be helpful. Using SQL Server 2008, if that matters.

Upvotes: 0

Views: 53

Answers (1)

Matthew Lau
Matthew Lau

Reputation: 158

I suppose this would work

Select 
     Year
    ,Count(id) 'YearCount'
    ,SUM(CASE WHEN MONTH IN (1,2,3) THEN 1 ELSE 0 END) 'Q1 COUNT'
    ,SUM(CASE WHEN MONTH IN (4,5,6) THEN 1 ELSE 0 END) 'Q2 COUNT'
    ,SUM(CASE WHEN MONTH IN (7,8,9) THEN 1 ELSE 0 END) 'Q3 COUNT'
    ,SUM(CASE WHEN MONTH IN (10,11,12) THEN 1 ELSE 0 END) 'Q4 COUNT'

 FROM TABLE
 GROUP BY Year

Upvotes: 3

Related Questions