CODEWITHSUNDEEP
sqlsql-server
arp
arp

Reputation: 73

JOIN, GROUP BY AND SUM in single Query

Invoices Table

 invoice_id    invoice_date
------------  --------------
     1          2013-11-27
     2          2013-10-09
     3          2013-09-12

Orders Table

order_id    invoice_id  product     quantity    total
---------   ----------  ---------   ---------   -------
   1            1       Product 1      100       1000
   2            1       Product 2      50        200
   3            2       Product 1      40        400
   4            3       Product 2      50        200

And i want a single sql query that produces following result

products    Month 9 Total   Month 10 Total   Mont 11 Total
--------    -------------   --------------   -------------
Product 1         0              400              100    
Product 2        200              0               200

I have tried the following sql query

SELECT orders.products, DATEPART(Year, invoices.invoice_date) Year, DATEPART(Month, invoices.invoice_date) Month, SUM(orders.total) [Total],
FROM invoices INNER JOIN orders ON invoices.invoice_id=orders.invoice_id
GROUP BY orders.products, DATEPART(Year, invoices.invoice_date), DATEPART(Month, invoices.invoice_date)

But it returns nothing. Is it possible to get this result with single query and what should i do for that ? Thanks

Upvotes: 0

Views: 140

Answers (3)

Obsidian Phoenix
Obsidian Phoenix

Reputation: 4155

One way to do it is with Cases:

Select 
    O.product,
    Sum(Case
        When DATEPART(M, I.invoice_Date) = 9 Then O.total
        Else 0
    End) as Month9,
    Sum(Case
        When DATEPART(M, I.invoice_Date) = 10 Then O.total
        Else 0
    End) as Month10,
    Sum(Case
        When DATEPART(M, I.invoice_Date) = 11 Then O.total
        Else 0
    End) as Month11
From Invoice I
    Left join Orders O on I.invoice_id = O.invoice_id
Group by O.product

There is another way using Pivots (depending on your version of SQL).

Select product, [9], [10], [11]
From 
(
    Select O.Product, O.total, DatePart(M, I.Invoice_Date) as [MonthNum]
    From Invoice I
        Left join Orders O on I.invoice_id = O.invoice_id
) P
PIVOT
(
    Sum(P.total)
    For [MonthNum] in ([9], [10], [11])
) as O

Upvotes: 0

Mithrandir
Mithrandir

Reputation: 25337

I think you want to use PIVOT here ...

Try this:

WITH tmp
AS 
(
SELECT orders.products, 
       DATEPART(Year, invoices.invoice_date) Year, 
       DATEPART(Month, invoices.invoice_date) Month, 
       SUM(orders.total) [Total]
FROM invoices INNER JOIN orders ON invoices.invoice_id = orders.invoice_id
GROUP BY 
        orders.products, 
        DATEPART(Year, invoices.invoice_date), 
        DATEPART(Month, invoices.invoice_date)
)
SELECT products, 
       ISNULL([9],0) AS Nine, ISNULL([10],0) AS Ten, ISNULL([11],0) as Eleven
FROM tmp
PIVOT
(
   SUM([Total])
   FOR Month IN
   ( [9], [10], [11])
) as PVT;

You can edit it here: http://sqlfiddle.com/#!6/6f80f/6

Upvotes: 2

AdamL
AdamL

Reputation: 13141

You better use pivot for this. Just remember that you have to list every month explicitly in pivot..for..in clause.

select 
    *
into #invoices
from (
    select 1 as invoice_id, '2013-11-27' as invoice_date union all
    select 2,'2013-10-09' union all
    select 3,'2013-09-12' 
) x


select 
    *
into #orders
from (
    select 1 as order_id,1 as invoice_id,'Product 1' as product,100 as quantity,1000 as total union all
    select 2,1,'Product 2',50,200 union all
    select 3,2,'Product 1',40,400 union all
    select 4,3,'Product 2',50,200
) x

GO


select 
    Product, 
    [9], [10], [11]
from (
    select 
        o.product, datepart([month],i.invoice_date) as mon,
        o.total
    from #invoices i
    join #orders o
        on i.invoice_id=o.invoice_id
) x
pivot (
    sum(total) for mon in ([9],[10],[11])
) p

Upvotes: 0

Related Questions