SQL query to aggregate month in one table-CodePudding

I have a table with one column (TK) with multiple values, also duplicated and another one column with date.

I need to return a table with first column with distinct(TK) and the other columns like month.

I do an example into SQL FIDDLE

TK	JANUARY
open a	4
open B	4

TK	FEBRUARY
open a	4
open B	4

I need

TK	JANUARY	FEBRUARY
open a	4	4
open B	4	4

Thanks

CodePudding user response：

A simple conditional aggregation should do the trick

SELECT TK
      ,Janary   = sum( case when month(datastart)=1 then 1 else 0 end )
      ,February = sum( case when month(datastart)=2 then 1 else 0 end )
 From  TEST
 Where year(datastart)=2021
 Group By TK

Or you can use PIVOT

Select *
 From  (
        Select TK
              ,Col = datename(month,DataStart) 
              ,Val = 1
         From TEST
         Where year(datastart)=2021
       ) src
 Pivot ( sum(Val) for Col in ([January] ,[February] ) ) pvt

CodePudding user response：

There are multiple ways to do this, but avoiding sub-queries and making the syntax simple to read, this is the simplest I can get:

SELECT
  TK,
  SUM(
    CASE WHEN DATASTART >= '2021-01-01' AND DATASTART < '2021-02-01' THEN 1 ELSE 0 END
  ) AS JENUARY,
  SUM(
    CASE WHEN DATASTART >= '2021-02-01' AND DATASTART <= '2021-02-28' THEN 1 ELSE 0 END
  ) AS FEBRUARY
FROM
  Test
GROUP BY
  TK

Check it out http://sqlfiddle.com/#!18/14cb9f/34