CODEWITHSUNDEEP
mysql
tarokins
tarokins

Reputation: 156

How to SUM query 3 tables?

A table
--------------------
id bId        txnVolume
1  1(b table) 10.00
2  1          5.00
3  2          7.00
4  3          2.00

B table
--------------------
id cId
1  1(C table)
2  2
3  3

C table
--------------------
id cusId prodId
1  1     1
2  1     2
3  1     1
4  1     2

I want to get the sum of txnVolume on table A if C table is custId 1 and prodId 1? Thanks for the help guys! Cheers!

Upvotes: 1

Views: 61

Answers (5)

xQbert
xQbert

Reputation: 35323

Select sum(coalesce(txnVolume,0))
FROM A INNER JOIN B on A.BID = B.Bid
INNER JOIN C on C.ID = B.CID
WHERE C.CustID = 1 and C.ProdID = 1

Upvotes: 1

John Woo
John Woo

Reputation: 263693

SELECT  SUM(a.txnVolume) totals
FROM    tableA a
        INNER JOIN tablB b
            ON a.bid = b.id
        INNER JOIN tableC c
            ON b.cid = c.ID
WHERE   c.custID = 1 AND c.prodID = 1

Upvotes: 1

Mikhail Vladimirov
Mikhail Vladimirov

Reputation: 13890

SELECT SUM(a.txnVolume)
FROM c 
  LEFT JOIN b 
    ON c.id=b.cId 
  LEFT JOIN a 
    ON b.id=a.bId 
WHERE c.cusId=1 AND c.prodId=1;

Upvotes: 1

Hogan
Hogan

Reputation: 70513

In sql you need to join the tables. When tables are joined they create one big table. You can then add where clauses to the columns.

SELECT sum(txnVolume)
FROM A
JOIN B on A.bId = B.id
JOIN C on B.cID = c.id
where c.custid = 1 and c.prodid = 1

Upvotes: 1

Joe Mastey
Joe Mastey

Reputation: 27099

Try the following: 

select sum(txnVolume) from A
   join B on A.bID = B.id
   join C on C.cID = C.id
  where prodID = 1 and custID = 1

Upvotes: 1

Related Questions