CODEWITHSUNDEEP
sqlsql-servergroup-byaggregation
Leeya
Leeya

Reputation: 170

Sql query for aggregation

Let consider the below table structure

Product    Year   Month Price

A         2011      01   23

A         2011      02   34

.......

.....

A       2011      12    54   
B       2011      01   13

B       2011      02   12

.......

.....

B 2011 12 20

From this table i need to aggregate the value for every 3 months ie..,

Product        Year          Month              Price

A              2011          1-3                45

A              2011          4-6                23

A              2011          7-9                45

A              2011          10-12              16

A 2012 1-3 12 ....... .......

Can anybody tell me how to do this calculation using sql query... Thanks in Advance !!!

Upvotes: 2

Views: 95

Answers (5)

Pரதீப்
Pரதீப்

Reputation: 93754

Try this. Use Case statement to group the month Quarter wise then find the sum of price

SELECT product,
       [year],
       CASE
         WHEN ( [month] ) IN( '01', '02', '03' ) THEN '1-3'
         WHEN ( [month] ) IN( '04', '05', '06' ) THEN '4-6'
         WHEN ( [month] ) IN( '07', '08', '09' ) THEN '7-9'
         WHEN ( [month] ) IN( '10', '11', '12' ) THEN '10-12'
       END [Month],
       Sum(Price)
FROM   tablename
GROUP  BY product,
          [year],
          CASE
            WHEN ( [month] ) IN( '01', '02', '03' ) THEN '1-3'
            WHEN ( [month] ) IN( '04', '05', '06' ) THEN '4-6'
            WHEN ( [month] ) IN( '07', '08', '09' ) THEN '7-9'
            WHEN ( [month] ) IN( '10', '11', '12' ) THEN '10-12'
          END

Note : By looking at your month data it looks like its a varchar column but you can change it to TINYINT

Upvotes: 3

Saravana Kumar
Saravana Kumar

Reputation: 3729

Use this query.

CREATE table #test
(
    product VARCHAR(10),
    year int, 
    month int, 
    price int
)

INSERT INTO #test
SELECT 'A', 2011, 01, 23 UNION
SELECT 'A', 2011, 02, 65 UNION
SELECT 'A', 2011, 03, 45 UNION
SELECT 'B', 2011, 04, 34 UNION
SELECT 'B', 2011, 05, 67 UNION
SELECT 'B', 2011, 06, 34 UNION
SELECT 'B', 2011, 07, 87 UNION
SELECT 'B', 2011, 08, 2 UNION
SELECT 'B', 2011, 09, 345 UNION
SELECT 'B', 2011, 10, 9 UNION
SELECT 'B', 2011, 11, 293 UNION
SELECT 'B', 2011, 12, 23 

    SELECT product, [year], [MONTH], SUM(price)
    FROM
    (

    SELECT product,  [year], CASE WHEN [MONTH] IN (01,02,03) THEN '1-3'
                               WHEN [MONTH] IN (04,05,06) THEN '4-6'
                               WHEN [MONTH] IN (07,08,09) THEN '7-9'
                               WHEN [MONTH] IN (10, 11, 12) THEN '10-12'
                         END AS [Month],    
            price
        FROm #Test
    )AS A
    group BY product, [year], [Month]

Upvotes: 0

Faisal
Faisal

Reputation: 1397

select Product, [year], case when [month] between 1 and 3 then '01-03' else case when [month] between 4 and 6 then '04-06' else case when [month] between 7 and 9 then '07-09' else '10-12' end end end, sum([price])

from Table1

group by Product, [year], case when [month] between 1 and 3 then '01-03' else case when [month] between 4 and 6 then '04-06' else case when [month] between 7 and 9 then '07-09' else '10-12' end end end

Above sql query will give you the required results, if you don not use leading zeros which I used in month labels then you will not get result sorted by month.

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10274

Considering Month is stored as varchar(), you can write as:

Select Product,
       Year,
       [MonthRange] as [Month],
       Sum(Price)
From (
select Product,
       Year,
       case when Month in ('01','02','03') then '[1-3]'
            when Month in ('04','05','06') then '[4-6]'
            when Month in ('07','08','09') then '[7-9]'
            when Month in ('10','11','12') then '[10-12]'
       end as [MonthRange],
       Price
from @test) as T
Group by Product,Year,[MonthRange]

DEMO

Upvotes: 0

Igor Borisenko
Igor Borisenko

Reputation: 3866

Try using CASE in GROUP BY clause:

...
GROUR BY [Year], CASE WHEN Month in (1,2,3) THEN '1-3'
                      WHEN Month in (4,5,6) THEN '4-6'
                  ...
                 END

Upvotes: 0

Related Questions