CODEWITHSUNDEEP
sqlsql-server
Orion77
Orion77

Reputation: 138

SQL query to aggregate month in one table

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

http://sqlfiddle.com/#!18/14cb9f/28

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

Upvotes: 1

Views: 67

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 1

AhmedMSedeek
AhmedMSedeek

Reputation: 51

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

Upvotes: 0

Related Questions