We want to see a breakdown of fees charged by year and week.
Here we use some interesting functions, such as DATEPART, which returns a specified portion of a date value, as well as more commonly used aggregate functions like SUM, MIN and MAX. We also use the CAST function to return the result of the AVG function as a number with two decimal places. We also add a little spice by throwing in the ROLLUP function to give us totals and subtotals. Where you see NULL in the results, interpret it as meaning “ALL”. Therefore a NULL in the YEAR column should be interpretted as the total line for all the years.
SELECT DATEPART(yy,trandate) AS "YEAR", DATEPART(wk,trandate) AS "WEEK_Number", MIN(trandate) AS "FROM", MAX(trandate) AS "THROUGH", CAST (AVG(amount) AS DECIMAL(12,2)) AS "AVERAGE FEE", SUM(amount) AS "TOTAL FEES CHARGED" FROM sos.journal WHERE trandate BETWEEN '2000-01-01' AND '2003-12-31' AND licnum = 101 AND trantype = 'S' GROUP BY ROLLUP("YEAR","WEEK_Number" ) ORDER BY "YEAR","WEEK_Number"