how to query sum for one field of two mysql tables

Question

I have two mysql table. Table: bill

id | billtype | amount | advid |   paydate  |adjid |  adjdate   |
 1 | electric | 10000  |  123  | 2017-01-01 | 50   | 2017-01-03 |
 2 |   Water  |  5000  |  124  | 2017-02-01 |  0   | 0000-00-00 |
 3 |   Shirt  |   500  |  125  | 2017-03-01 |  0   | 0000-00-00 |

Table: advance

 id | advid | amount | balance | purpose |
 1  | 123   | 50000  |  20000  |   Bill  |
 2  | 124   | 70000  |  10000  |   Bill  |
 3  | 125   | 55000  |  15000  | Uniform |
 4  | 124   | 60000  |  10000  |   Bill  |

I want to create a drop down menu so that to select those 'advance' which are not adjusted yet (adjid=0 and adjdate=0000-00-00) in Table: bill and that drop down menu will also contain the total value of advance for same advance id (advid) like below:

<option>Bill-130000</option>
<option>Uniform-55000</option>

As total 130000 (70000+60000) advance is taken against advance id 124, so the Total amount of Advance in Option menu should be 130000 in case of Bill. But I am failed to calculate total amount of advance accurately:

SELECT sum(a.amount), purpose FROM bill as b, advance as a WHERE b.paydate!='0000-00-00' AND b.adjid!=0 AND a.advid=b.advid GROUP BY a.advid

Total amount in <option></option> is not coming actual.

What would be the right query for this purpose?

The results you got are correct. The condition b.paydate!='0000-00-00' AND b.adjid!=0 will give only advid= 123 from bill table, therefore will give you only 50000. — user8608089
– user8608089, Commented Sep 21, 2017 at 6:37
Typically, you'd GROUP by the non-aggregated columns in your SELECT. — Strawberry
– Strawberry, Commented Sep 21, 2017 at 6:46

Trung Duong · Accepted Answer · 2017-09-21 07:01:40Z

2

You could try

SELECT SUM(a.amount) AS amount, 
       MAX(purpose)  AS purpose 
FROM   advance a 
WHERE  a.advid IN (
               SELECT b.advid 
               FROM   bill b 
               WHERE  b.paydate = '0000-00-00' 
                      AND b.adjid = 0) 
GROUP  BY a.advid

answered Sep 21, 2017 at 7:01

Trung Duong

3,4752 gold badges10 silver badges10 bronze badges

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

1 Comment

Abdullah Mamun-Ur- Rashid Over a year ago

Great! Thanks for your Code.

Collectives™ on Stack Overflow

how to query sum for one field of two mysql tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related