question

Geetha1990 avatar image
Geetha1990 asked

Query to display sum of transactions quarterly while dealing with multiple years

I need a SQL query to display sum by quarter of the year. For example: Name Q12016 Q22016 Q32016 Q42016 Q12017 Q22017 Q32017 Q42017 XXX 10 0 1 5 0 0 2 10 YYY 0 1 2 3 4 5 6 7
querysql server 2008r2
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

please share what you have tried so far
0 Likes 0 ·
imravipaul avatar image
imravipaul answered
SELECT DATEPART(YEAR,fdPaymentDate)[YEAR], CAST(DATEPART(QUARTER,fdPaymentDate) AS VARCHAR(10))+'ST' MONTH, SUM(fnAmount ) [TOTAL] FROM tblPaymentDetail GROUP BY DATEPART(YEAR,fdPaymentDate),DATEPART(QUARTER,fdPaymentDate) ORDER BY YEAR,MONTH
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
So, given your suggested output, it seems like you today have: Name Date NumItems And you want each YEAR/QUARTER combination to be a column of its own, where each row contains the sum of items per name. Is that right? If so, you need to first transform the date to YEAR/QUARTER-combination, and then you need to use PIVOT to get rows to columns. Something like this: WITH CTE AS ( SELECT Name, Items, 'Q' + CAST(DATEPART(QUARTER,datecolumn) as char(1)) + DATENAME(YEAR,datecolumn) as QY FROM TheTable )SELECT Name, Q12016, Q22016, Q32016, Q42016, Q12017, Q22017, Q32017, Q42017 FROM (SELECT * FROM CTE) as src PIVOT (SUM(Items) FOR datecolumn IN([Q12016],[Q22016],[Q32016],[Q42016],[Q12017],[Q22017],[Q32017],[Q42017])) as Pvt
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.