SQL Sumation from Different Tables

Question

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.

Can you provide us with some sample data and desired result ? — GuidoG
– GuidoG, Commented Sep 21, 2018 at 13:47
@BartoszSiemasz as far as I understand there is no Brand table, PID is not unique. — Lajos Arpad
– Lajos Arpad, Commented Sep 21, 2018 at 13:51

Gordon Linoff · Accepted Answer · 2018-09-21 13:51:25Z

1

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;

answered Sep 21, 2018 at 13:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ian · Accepted Answer · 2018-09-22 11:44:43Z

1

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

edited Sep 22, 2018 at 11:44

answered Sep 21, 2018 at 14:03

Ian

554 bronze badges

2 Comments

Muzamil Sajid Over a year ago

Hi Ian, i would like both the summations of Invoice and Cash to be in one column only and not 2 separate columns. I also would like only 2 columns to be visible i.e. Brand and TotalSales.

Ian Over a year ago

amended to reflect your requirement :) I'd have a look on youtube for some starter videos on SQL.. there are lots

Lajos Arpad · Accepted Answer · 2018-09-21 14:05:42Z

0

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.

edited Sep 21, 2018 at 14:05

answered Sep 21, 2018 at 13:49

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

Comments

GuidoG · Accepted Answer · 2018-09-21 14:13:00Z

0

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

edited Sep 21, 2018 at 14:13

answered Sep 21, 2018 at 13:52

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

Collectives™ on Stack Overflow

SQL Sumation from Different Tables

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related