CODEWITHSUNDEEP
mysqldatabase
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

Calculating using SUM, query does not work?

I have two tables.

a) ai_account

enter image description here

b) ai_order_product

enter image description here

i want to do some calculations for particular supplier_id.

1, totalAmount, i want to do something like SUM(ai_order_product.quantity * ai_order_product.cost)

2, amountPaid, this is total amount paid by the supplier that will be something like SUM(ai_account.amount) with reference to supplier_id.

3) balance, this will be calculated by SUM(ai_order_product.quantity * ai_order_product.cost) - SUM(ai_invoice.amount)

4) lastPayment date, that will be MAX(ai_account.addDate).

i tried doing something like this.

SELECT SUM(op.quantity * op.cost) as totalAmount, 
       SUM(ac.amount) as amountPaid, 
       SUM(op.quantity * op.cost) - SUM(ac.amount) as balance, 
       MAX(ac.addDate) as lastPayment 
    FROM ai_order_product op 
        LEFT JOIN ai_account ac 
            ON (op.supplier_id = ac.trader_id) 
    WHERE op.supplier_id = 42

it does not work properly, it fetches some unexpected values, whereas the result for the above being expected is,

for supplier_id = 42,
1) totalAmount = 1375,
2) amountPaid = 7000,
3) balance = -5625,
4) lastPayment = 2011-11-23

and for supplier_id = 35,
1) totalAmount = 1500,
2) amountPaid = 43221,
3) balance = -41721,
4) lastPayment = 2011-11-28

and for supplier_id = 41
1) totalAmount = 0
2) amountPaid = 3000,
3) balance = -3000,
4) lastPayment = 2011-11-09

i want to fetch one row by supplier_id.

P.S: i just entered some dummy values that is why the calculations are mostly negative whereas in application the calculated values will be positive.

Upvotes: 0

Views: 1720

Answers (5)

Derek
Derek

Reputation: 23228

Thats because each "ai_order_product" row is getting counted multiple times (once for each row present in the ai_account table).

Try this:

SELECT 
  op.totalAmount as totalAmount
  , SUM(ac.amount) as amountPaid
  , op.totalAmount - SUM(ac.amount) as balance
  , MAX(ac.addDate) as lastPayment 
FROM (
  select supplier_id, sum(quantity * cost) as totalAmount 
  from ai_order_product
  group by supplier_id) op 
LEFT JOIN ai_account ac ON (op.supplier_id = ac.trader_id) 
WHERE op.supplier_id = 42

This may be slightly off, but this general logic should work.

Upvotes: 2

Johan
Johan

Reputation: 76547

SELECT  
  SUM(op.quantity * op.cost) as totalAmount 
  , ac2.amountPaid 
  , SUM(op.quantity * op.cost) - ac2.balance 
  , ac2.lastPayment  
FROM ai_order_product op  
LEFT JOIN (SELECT 
             ac.supplier_id
             , MAX(ac.addDate) as lastPayment
             , SUM(ac.amount) as balance 
           FROM ai_account ac 
           WHERE (op.supplier_id = ac.supplier_id)
           GROUP BY ac.supplier_id) ac2 ON (ac2.supplier_id = op.supplier_id)  
WHERE op.supplier_id = 42 
GROUP BY op.supplier_id

The group by clauses kick in when you're selecting more than one supplier_id.

Upvotes: 1

xQbert
xQbert

Reputation: 35323

I think you need to first get the sub totals for one of the tables so that you only have 1 row coming back. thus the inner select here returns the max and the sum, so when you join to it you only get 1 row.

SELECT SUM(op.quantity * op.cost) as totalAmount, 
       ac.addDate as lastPayment, 
       ac.amount as amountPaid, 
       SUM(op.quantity * op.cost) - SUM(ac.amount) as balance
    FROM ai_order_product op 
        INNER JOIN (
            SELECT max(IaiaddDate) as addDate, sum(iai.amount) as Amount, iai.supplier_ID 
            FROM ai_account iai 
            Group by supplier_ID) ac 
        ON AC.Supplier_ID = Op.Supplier_ID
    WHERE op.supplier_id = 42

Group by Ac.addDate, ac.amount, op.supplier_ID --Just incase where clause is left off.

Upvotes: 0

Marc B
Marc B

Reputation: 360612

Notice that your expected and actual values are doubled. After entering your sample data and running the query, I get (for supplier_id = 42)

+-------------+------------+---------+-------------+
| totalAmount | amountPaid | balance | lastPayment |
+-------------+------------+---------+-------------+
|        2750 |      14000 |  -11250 | 2011-11-23  |
+-------------+------------+---------+-------------+

That's because you've got 2 rows in each table that match the join criteria, causing a doubling of the results.

Upvotes: 0

Andreas Krueger
Andreas Krueger

Reputation: 1507

You have to use to state GROUP BY, when using aggregate functions like SUM in SELECT statements.

SELECT op.supplier_id as supplierId,
       SUM(op.quantity * op.cost) as totalAmount, 
       SUM(ac.amount) as amountPaid, 
       SUM(op.quantity * op.cost) - SUM(ac.amount) as balance, 
       MAX(ac.addDate) as lastPayment 
    FROM ai_order_product op 
        LEFT JOIN ai_account ac 
            ON (op.supplier_id = ac.trader_id)
    GROUP BY op.supplier_id
    HAVING supplierId = 42

Upvotes: 1

Related Questions