I have in SQL-Server a table like this

I need to create a view that gets the following data:

I know how to count by month but I struggle to then split the counting into two different rows, one for each year. Can anyone give me a hint?

Thanks a lot

CodePudding user response：

Group by year(CreatedAt) does the trick, with a case expression inside a sum or count for each month and the total:

select 
year(CreatedAt) as Year,
sum(case when month(CreatedAt) = 1 then 1 else 0 end) as Jan,
sum(case when month(CreatedAt) = 2 then 1 else 0 end) as Feb,
sum(case when month(CreatedAt) = 3 then 1 else 0 end) as March,
sum(case when month(CreatedAt) = 4 then 1 else 0 end) as April,
sum(case when month(CreatedAt) = 5 then 1 else 0 end) as May,
sum(case when month(CreatedAt) = 6 then 1 else 0 end) as June,
sum(case when month(CreatedAt) = 7 then 1 else 0 end) as July,
sum(case when month(CreatedAt) = 8 then 1 else 0 end) as Aug,
sum(case when month(CreatedAt) = 9 then 1 else 0 end) as Sept,
sum(case when month(CreatedAt) = 10 then 1 else 0 end) as Oct,
sum(case when month(CreatedAt) = 11 then 1 else 0 end) as Nov,
sum(case when month(CreatedAt) = 12 then 1 else 0 end) as Dec,
count(*) as Total
from table_name
group by year(CreatedAt);

Fiddle

CodePudding user response：

You could also use a PIVOT. Though personally I find Zakaria's approach more compact for this specific case.

db<>fiddle

SELECT 
      YearNum
      , [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 ColumnA
          , DatePart(yyyy, CreatedAt) AS YearNum
          , Left(DateName(mm, CreatedAt), 3) AS MonthName
    FROM   YourTable
) t 
PIVOT (
   COUNT(ColumnA)
   FOR MonthName IN (
      [Jan]
      , [Feb]
      , [Mar]
      , [Apr]
      , [May]
      , [Jun]
      , [Jul]
      , [Aug]
      , [Sep]
      , [Oct]
      , [Nov]
      , [Dec]
   )
) AS pvt