CODEWITHSUNDEEP
mysql
Laravelian
Laravelian

Reputation: 598

How can I compare sums of two tables in a single query?

http://sqlfiddle.com/#!2/fae8a/6

I've got three tables -- orders, order_line_items, order_payments.

I can aggregate the totals and payments of the orders using a select statement, but I can't seem to figure out the right syntax to return only orders that have 'payments' that add up to less than 'total' (orders not paid in full).

What am I missing?

Upvotes: 0

Views: 65

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You need to use HAVING clause to filter results by aggregate functions,you also need to use COALESCE() to filter out the null

select 
    `orders`.`id`, 
    `order_payments`.`order_id`, 
    `order_payments`.`amount`, 
    `order_line_items`.`order_id`, 
    `order_line_items`.`price_each`, 
    `order_line_items`.`quantity`,
    COALESCE(SUM(`order_line_items`.`price_each` * `order_line_items`.`quantity`),0) as `order_total`,
    COALESCE(SUM(`order_payments`.`amount`),0) as `payments`
from 
    `orders` 
left join 
    `order_payments` on `order_payments`.`order_id` = `orders`.`id` 
left join 
    `order_line_items` on `order_line_items`.`order_id` = `orders`.`id`
group by 
    `orders`.`id`
HAVING   `payments` < `order_total`

Updated fiddle

EDIT from comments

Another way to rewrite your query is using subquery your previous query is giving you the Cartesian product 

select 
    `o`.`id`,
(SELECT COALESCE(SUM(`price_each` * `quantity`),0) FROM `order_line_items` WHERE order_id =`o`.`id` ) as `order_total`,
(SELECT COALESCE(SUM(`amount`),0) FROM `order_payments` WHERE order_id =`o`.`id`) as `payments`
from 
    `orders` o
HAVING   `payments` < `order_total`

Without Having Clause

With Having Clause

Upvotes: 2

Neil Hampton
Neil Hampton

Reputation: 1883

You can't use where on an sum() use having instead.

Upvotes: 1

Related Questions