MySQL SUM Query

Question

Greetings, I've got a query that I'm struggling with, this is the first time that I am encountering this type of query. I have two table as shown below.

xid is the primary key in parent_tbl1, while xid is the foreign key in child_tbl2

parent_tbl1

child_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       1    1    1

Below is the exlanation of the query in parts, which will then need to make up the whole query.

I need the SUM of qnty in child_tbl2:

1) Who's parent's pub is '1' Therefore, id 5 is eliminated from child_tbl2, this is because xid 3 is 0 in parent_tbl1

Results: child_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
6  A       1    4    3
7  A       1    4    1
8  A       1    1    1

2) AND who's parent table has ttype 'A' in the child table Therefore, id 3 is eliminated from the existing results because id 3's ttype is B

Results: child_tbl2

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

3) AND who's parent has '0' as one it's fno's in the child_tbl2 Therefore, id 4, 6 & 7 are eliminated from the existing results, this is because 0 was not found in one of their fno's, while 0 was found as one of xid 1's fno

Results: child_tbl2

id ttype fno xid qnty
1  A       0    1    0
2  A       1    1    3
8  A       1    1    1

The answer for the query should be 4

Below is what i've got.

SELECT sum(child_tbl2.qnty), parent_tbl1.xid, parent_tbl1.pub, child_tbl2.ttype, child_tbl2.fno, child_tbl2.xid 
FROM parent_tbl1, child_tbl2
WHERE parent_tbl1.xid = child_tbl2.xid
AND parent_tbl1.pub = '1'
AND child_tbl2.ttype = 'A'

AND child_tbl2.fno ?

If it is possible, I do not know how to tell the dbms (MySQL) to check if Zero is one of the fno's. If I say "AND child_tbl2.fno = '0'", I will be saying that the result's fno should be 0. I do not want that, I need zero to be one of the fno's in order for the query to SUM all the qnty in that particular xid

Ignacio Vazquez-Abrams · Accepted Answer · 2010-09-10 12:40:09Z

2

SELECT SUM(DISTINCT src.qnty) as qnty
FROM tbl2 AS src
INNER JOIN tbl1 AS pub
  ON src.xid=pub.xid
INNER JOIN tbl2 AS fno
  ON pub.xid=fno.xid
WHERE pub.pub=1
  AND src.ttype='A'
  AND fno.fno=0

edited Sep 10, 2010 at 12:40

answered Sep 10, 2010 at 12:08

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

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

3 Comments

Nich Over a year ago

Hi, should "src.qty" be "child_tbl2"?

Ignacio Vazquez-Abrams Over a year ago

No. AS gives a table or column an alias that should be used everywhere.

Nich Over a year ago

I replaced tbl1 with parent_tbl1 and tbl2 with child_tbl2 and this is the error that I got - #1054 - Unknown column 'src.qty' in 'field list' Any ideas?

Collectives™ on Stack Overflow

MySQL SUM Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related