SUM YTD and MTD in the same query

Question

i have a table TABLE1 :

  Key     dt    category    AmtBC
 2475   201402  Penzijni    100,00
 2475   201403  Penzijni    100,00
 2475   201311  Retail     2056,00
 2475   201312  Retail     978,00
 2475   201406  Penzijni   100,00
 2475   201406  Retail     410,00
 2475   201401  Penzijni   100,00
 2475   201407  Retail     179,00
 2475   201404  Penzijni    100,00
 2475   201405  Penzijni    100,00
 2475   201407  Penzijni    100,00
 2475   201401  Retail      1377,00
 9628   201404  Penzijni    500,00
 9628   201405  Penzijni    500,00
 9628   201402  Penzijni    500,00
 9628   201406  Penzijni    500,00
 9628   201401  Penzijni    500,00
 9628   201407  Penzijni    500,00
 9628   201403  Penzijni    500,00

And i need to sum the AmtBC for each Key, group by Dt and category. BUT i need to count category Penzijni as MTD and Retail as YTD.

So when i do this :

SELECT 
    KEY
    , Dt
    , SUM(CASE 
            WHEN category = 'Penzijni'
              THEN AmtBC
            ELSE 0
          END) AS Penzijni
    , SUM(CASE 
            WHEN category = 'Retail'
              THEN AmtBC
          ELSE 0
          END) AS Retail
FROM TABLE1
GROUP BY KEY, dt

it gives me the sum by moths, but i need in the same query to SUM Penzijni as now (monthly) but the Retail by year , that means, SUM by every year,like this:

Key    Dt    category    AmtBC
2475  201311  Retail         3034
2475  201312  Retail         3034
2475  201401  Retail         2066
2475  201406  Retail         2066
2475  201407  Retail         2066  

Thank you in advance

EDITED: i changed the definition for YTD format

Answer 1

Try this:

SELECT 
[KEY]
, Dt
, SUM(CASE 
        WHEN category = 'Penzijni'
          THEN AmtBC
        ELSE 0
      END)  OVER (PARTITION BY [key], dt order by dt) AS Penzijni
, SUM(CASE 
        WHEN category = 'Retail'
          THEN AmtBC
      ELSE 0
      END)  OVER (PARTITION BY [key], left(dt,4) order by dt) AS Retail
FROM table1

The idea is to use the SUM function with an OVER clause, and then partition the source data set by either month-year or just year. This gives us more flexibility in that we can specify separate grouping criteria for the 2 categories i.e. by year for Retail and by month for Penzijni, when we calculate the sum.

Demo

Alternatively, you can separate the grouping into 2 queries and then join based on the key, like so:

;with retail as
(SELECT [key], left(Dt,4) y, SUM(CASE WHEN category = 'Retail' THEN AmtBC ELSE 0 END) Retail
FROM tbl
GROUP BY [KEY], left(dt,4)),

penzijni as
(SELECT [key], Dt m,SUM(CASE WHEN category = 'Penzijni' THEN AmtBC ELSE 0 END) Penzijni
FROM tbl
GROUP BY [KEY], dt)

select p.[key], p.m Dt, r.Retail, p.Penzijni from
retail r 
inner join penzijni p on r.[key] = p.[key]

Demo