CODEWITHSUNDEEP
sqlsql-serverdatabase
Muzamil Sajid
Muzamil Sajid

Reputation: 25

SQL Sumation from Different Tables

I would like some help on this matter.

I Have three tables.

Products Table, Invoice Table, Cash Table

Products Table has 2 Columns as follows (PID,BRAND)

Invoice Table has 2 columns as follows (PID,TOTALSALES)

Cash Table has 2 columns as follows (PID,TOTALSALES)

The PID's could have many different BRANDS.

for eg:

PID   BRAND
1     TEST1
2     TEST2
3     TEST3

All the three tables are linked by the PID column.

i have many rows in the invoice and cash tables.

My problem is to Group the SUMMED values of the TOTALSALES in the INVOICE and CASH tables BRAND wise.

I tried using left joins but the summation value always increases when there are more than 2 table in the join.

Any help will be highly appreciated.

Upvotes: 0

Views: 59

Answers (4)

Ian
Ian

Reputation: 55

summarise the data in the Invoice and Cash tables separately 

 SELECT
    pr.BRAND, (ISNULL(Inv.TOTALSALES, 0.00) + ISNULL(Csh.TOTALSALES, 0.00)) TotalSales
FROM Products pr
LEFT JOIN (
    SELECT
        PID, SUM(TOTALSALES) TOTALSALES
    FROM Invoice GROUP BY PID
) Inv ON Inv.PID = pr.PID
LEFT JOIN  (
    SELECT
        PID, SUM(TOTALSALES) TOTALSALES
    FROM Cash GROUP BY PID
) Csh ON Csh.PID = pr.PID
ORDER BY pr.BRAND

Upvotes: 1

GuidoG
GuidoG

Reputation: 12014

another way to do it is by using subquerys 

select p.*,
       (select sum(i.TOTALSALES) from Invoice i where i.PID = p.PID) as TotalSalesInvoice,
       (select sum(c.TOTALSALES) from Cash c where c.PID = p.PID) as TotalSalesCash
from   Products p

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 76424

You need two separate sums for the results of joining the tables by PID values and you will need to group the records by BRAND

select sum(Invoice.TOTALSALES) as invoice_sales, sum(Cash.TOTALSALES) as cash_sales
from Products
join Invoice
on Products.PID = Invoice.PID
join Cash
on Products.PID = Cash.PID
group by Invoice.PID, Products.BRAND;

If you do not need to group by products, then just omit Invoice.PID from the group by.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use union all and then aggregate the results from the two tables together:

select p.brand, sum(invoice_sales) as invoice_sales, sum(cash_sales) as cash_sales
from ((select pid, totalsales as invoice_sales, 0 as cash_sales
       from invoice i
      ) union all
      (select pid, 0, totalsales
       from cash c
      )
     ) ic join
     product p
     on ic.pid = p.id
group by p.brand;

Upvotes: 1

Related Questions