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);

Get the top 5th salary of all employees

SELECT SALARY FROM

(SELECT VAL1, RANK() OVER (ORDER BY VAL1 DESC) R from TEMPX)

WHERE R=5;

OR

SELECT DISTINCT (a.sal) FROM EMP A WHERE 5= (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);