Return a record for each year

Question

I am trying to get this query to return a record for each year instead of returning a single record for everything in the table.

SELECT 
    JAN
    , FEB
    , MAR
    , APR
    , MAY
    , JUN
    , JUL
    , AUG
    , SEP
    , OCT
    , NOV
    , [DEC]
    ,JAN + FEB + MAR + APR + MAY + JUN + JUL + AUG + SEP + OCT + NOV + [DEC] AS TOTAL
    FROM
    (SELECT DISTINCT
        (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 1) AS JAN
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 2) AS FEB
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 3) AS MAR
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 4) AS APR
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 5) AS MAY
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 6) AS JUN
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 7) AS JUL
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 8) AS AUG
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 9) AS SEP
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 10) AS OCT
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 11) AS NOV
        , (SELECT COUNT(*) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 12) AS [DEC]
    FROM RET.tbl_Record)x

This returns 1 record (I know that is what is suppose to do) but I would like it to return a record for each year. I'm just not sure how to accomplish this.

EDIT:

SELECT 
    JAN
    , FEB
    , MAR
    , APR
    , MAY
    , JUN
    , JUL
    , AUG
    , SEP
    , OCT
    , NOV
    , [DEC]
    ,JAN + FEB + MAR + APR + MAY + JUN + JUL + AUG + SEP + OCT + NOV + [DEC] AS TOTAL
    FROM
    (SELECT
        (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 1) AS JAN
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 2) AS FEB
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 3) AS MAR
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 4) AS APR
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 5) AS MAY
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 6) AS JUN
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 7) AS JUL
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 8) AS AUG
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 9) AS SEP
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 10) AS OCT
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 11) AS NOV
        , (SELECT COUNT(dt_updated) FROM RET.tbl_Record WHERE MONTH(dt_updated) = 12) AS [DEC]
    FROM RET.tbl_Record
    GROUP BY YEAR(dt_updated))x

Now returns 3 records which is the correct amount of records I am looking for however each record returns the same values (it counts all three years in each record)

Answer 1

This will solve, for example, all of the months in 2011, 2012 and 2013, with far less repetitive code.

DECLARE @years TABLE(y INT);

INSERT @years SELECT 2011 UNION ALL SELECT 2012 UNION ALL SELECT 2013;

;WITH m(m) AS 
(
  SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
  FROM sys.all_objects ORDER BY [object_id]
),
dates(y,m) AS
(
  SELECT y.y, DATEADD(MONTH, m.m, DATEADD(YEAR, y.y - 1900, 0)) FROM m 
  CROSS JOIN @years AS y
),
s([YEAR],m,c) AS
(
  SELECT d.y, LEFT(UPPER(DATENAME(MONTH, d.m)),3), COUNT(r.dt_updated)
    FROM dates AS d LEFT OUTER JOIN RET.tbl_Record AS r
    ON r.dt_updated >= d.m AND r.dt_updated < DATEADD(MONTH, 1, d.m)
    GROUP BY d.y, DATENAME(MONTH, d.m)
),
n AS
(
  SELECT * FROM s PIVOT (MAX(c) FOR m IN 
    (JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,[DEC])) AS p
)
SELECT *,Total = JAN+FEB+MAR+APR+MAY+JUN+JUL+AUG+SEP+OCT+NOV+[DEC] FROM n
ORDER BY [YEAR];

Need to solve for different years? No problem, just change the hard-coded insert into @years.

Need it to be dynamic? Again, no problem; this will solve for every year found in the table:

INSERT @years SELECT DISTINCT YEAR(dt_updated) FROM RET.tbl_Record;

Need it to be ever more dynamic (e.g. the most recent three years in the table):

INSERT @years SELECT DISTINCT TOP (3) YEAR(dt_updated) 
  FROM RET.tbl_Record ORDER BY YEAR(dt_updated) DESC;

For average, sorry, you're on your own (you're changing requirements on me way too late in the game). My suggestion: do that in your reporting tool and/or presentation tier.

Answer 2

I was hoping for a more efficent way to accomplish the task but it looks like the solution is:

SELECT
      JAN.dtyear AS [YEAR]
    , ISNULL(JAN.TOTAL, 0) AS JAN
    , ISNULL(FEB.TOTAL, 0) AS FEB
    , ISNULL(MAR.TOTAL, 0) AS MAR
    , ISNULL(APR.TOTAL, 0) AS APR
    , ISNULL(MAY.TOTAL, 0) AS MAY
    , ISNULL(JUN.TOTAL, 0) AS JUN
    , ISNULL(JUL.TOTAL, 0) AS JUL
    , ISNULL(AUG.TOTAL, 0) AS AUG
    , ISNULL(SEP.TOTAL, 0) AS SEP
    , ISNULL(OCT.TOTAL, 0) AS OCT
    , ISNULL(NOV.TOTAL, 0) AS NOV
    , ISNULL([DEC].TOTAL, 0) AS [DEC]
    , ISNULL(JAN.TOTAL, 0) + ISNULL(FEB.TOTAL, 0) + ISNULL(MAR.TOTAL, 0) + ISNULL(APR.TOTAL, 0) + ISNULL(MAY.TOTAL, 0) + ISNULL(JUN.TOTAL, 0) + ISNULL(JUL.TOTAL, 0) + ISNULL(AUG.TOTAL, 0) + ISNULL(SEP.TOTAL, 0) + 
        ISNULL(OCT.TOTAL, 0) + ISNULL(NOV.TOTAL, 0) + ISNULL([DEC].TOTAL, 0) AS TOTAL
FROM 
    (SELECT COUNT(RET_record_id) as TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 1 GROUP BY YEAR(dt_updated)) JAN
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) as TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 2 GROUP BY YEAR(dt_updated)) FEB
     ON JAN.dtyear = FEB.dtyear
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 3 GROUP BY YEAR(dt_updated)) MAR
     ON JAN.dtyear = MAR.dtyear AND FEB.dtyear = MAR.dtyear
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 4 GROUP BY YEAR(dt_updated)) APR
     ON JAN.dtyear = APR.dtyear
    LEFT OUTER JOIN 
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 5 GROUP BY YEAR(dt_updated)) MAY
     ON JAN.dtyear = MAY.dtyear
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 6 GROUP BY YEAR(dt_updated)) JUN
     ON JAN.dtyear = JUN.dtyear
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 7 GROUP BY YEAR(dt_updated)) JUL
     ON JAN.dtyear = JUL.dtyear
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 8 GROUP BY YEAR(dt_updated)) AUG
     ON JAN.dtyear = AUG.dtyear
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 9 GROUP BY YEAR(dt_updated)) SEP
     ON JAN.dtyear = SEP.dtyear
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 10 GROUP BY YEAR(dt_updated)) OCT
     ON JAN.dtyear = OCT.dtyear
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 11 GROUP BY YEAR(dt_updated)) NOV
     ON JAN.dtyear = NOV.dtyear
    LEFT OUTER JOIN
    (SELECT COUNT(RET_record_id) AS TOTAL, YEAR(dt_updated) AS dtyear FROM RET.tbl_Record WHERE MONTH(dt_updated) = 12 GROUP BY YEAR(dt_updated)) [DEC]
     ON JAN.dtyear = [DEC].dtyear

Answer 3

perhaps :

GROUP BY YEAR(dt_updated)