MySQL SUM Query

Question

I've got two tables.

I'm trying to calculating the SUM quantity of tbl1

tbl1.xid is the primary, while tbl2.xid is the foreign

tbl1

xid pub quantity
1    1    10          
2    1    2      
3    0    1      
4    1    5

tbl2

id ttype fno xid qnty
1  A       0    1    0
2  A       1    1    3
3  B       1    1    4
4  A       1    2    1 
5  A       1    3    2
6  A       1    4    3
7  A       1    4    1
8  A       0    1    0

We are calculating the sum of tbl1's quantity

1) Whos tbl1.pub is 1 Thus tbl1.xid 3 is removed form the list, for it's pub is 0

Results

tbl1

xid pub quantity
1    1    10          
2    1    2      
4    1    5

2) AND Who's tbl1 has at least one tbl2.xid who's tbl2.ttype is 'A' and who's tbl2.fno is '0' Thus tbl1.xid 2 & 4 are removed form the list, because none of them have at least one tbl2.xid who's fno is '0' and who's tbl2.ttype is 'A'

Results

parent_tbl1

xid pub quantity
1    1    10

The final results should be 10

So, I've been playing with this and SELECT DISTINCT ( SUM( quantity ) ) AS qnts FROM tbl1 INNER JOIN tbl2 ON tbl1.xid = tbl2.xid WHERE pub =1 AND ttype = 'A' AND fno =0 GROUP BY tbl2.id gets me down to 10, but if I add another xid (book?) to tbl1 that has a qualifying record in tbl2, I get a table, with two records that actually add up to the total we're looking for. This is as far as I have time to get to right now, but this puzzle intrigues me, so I hope to continue on it later. — d2burke
– d2burke, Commented Sep 12, 2010 at 16:17

Martin Smith · Accepted Answer · 2010-09-12 16:35:36Z

1

SELECT SUM(quantity) AS Total
FROM   tbl1
WHERE  pub=1
AND    EXISTS
       (SELECT *
       FROM    tbl2
       WHERE   tbl2.ttype = 'A'
       AND     tbl2.fno   = 0
       AND     tbl1.xid   = tbl2.xid
       )

answered Sep 12, 2010 at 16:35

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

Collectives™ on Stack Overflow

MySQL SUM Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related