Monday, October 7, 2013

GROUP BY MONTH, YEAR AND WEEK IN SQL

Say, You have a table from which you need to get the count of records, sum of amount and average amount on a weekly, monthly and yearly basis.

These are the queries:

Select
COUNT(id) as COUNT, SUM(AMOUNT) as SUM, AVG(AMOUNT) as AVG, DATEPART(WEEK, CREATED_DATE) as WEEKCOUNT
from TABLE
GROUP BY DATEPART(WEEK, CREATED_DATE);

Select
COUNT(id) as COUNT, SUM(AMOUNT) as SUM, AVG(AMOUNT) as AVG, MONTH(CREATED_DATE) as MONTHCOUNT
from TABLE
GROUP BY MONTH(CREATED_DATE);

Select
COUNT(id) as COUNT, SUM(AMOUNT) as SUM, AVG(AMOUNT) as AVG, YEAR(CREATED_DATE) as YEARCOUNT
from TABLE
GROUP BY YEAR(CREATED_DATE);