CODEWITHSUNDEEP
phpmysqlsql-server
Doron Barel
Doron Barel

Reputation: 87

Combine 2 SQL queries into one - show gross & net sales

I have the following two queries - one showing gross sales and one showing paid sales (the only difference between the queries is an added condition - flag_payment = 1).

The output is two columns - Supplier Name and then Gross or Paid Sales in $ (gross - query #1, paid - query #2). If possible, I would like to combine the two queries so that the output will be Supplier Name, Gross Sales and Paid Sales (i.e. 3 columns).

Query 1: Gross Sales

SELECT C.value , SUM( SL.price ) 
FROM sales_listings SL
INNER JOIN sales S ON SL.sale_id = S.id
INNER JOIN custom_fields_data C ON SL.listing_id = C.owner_id
WHERE C.field_id =11
GROUP BY C.value

Query 2: Paid Sales

SELECT C.value , SUM( SL.price ) 
FROM sales_listings SL
INNER JOIN sales S ON SL.sale_id = S.id
INNER JOIN custom_fields_data C ON SL.listing_id = C.owner_id
WHERE S.flag_payment = 1 AND C.field_id =11
GROUP BY C.value

Is there a way to combine the following two queries? Thank you!

Upvotes: 2

Views: 398

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use conditional aggregation:

SELECT 
    c.value, 
    SUM(CASE WHEN s.flag_payment = 0 THEN sl.price ELSE 0 END) AS GrossSales,
    SUM(CASE WHEN s.flag_payment = 1 THEN sl.price ELSE 0 END) AS PaidSales
FROM sales_listings sl
INNER JOIN sales s 
    ON sl.sale_id = s.id
INNER JOIN custom_fields_data c 
    ON sl.listing_id = c.owner_id
WHERE
    c.field_id = 11
GROUP BY c.value

Since in your original query for the Gross Sales, there is no filter for s.flag_payment, you can omit the conditional SUM:

SELECT 
    c.value, 
    SUM(sl.price) AS GrossSales,
    SUM(CASE WHEN s.flag_payment = 1 THEN sl.price ELSE 0 END) AS PaidSales
FROM sales_listings sl
INNER JOIN sales s 
    ON sl.sale_id = s.id
INNER JOIN custom_fields_data c 
    ON sl.listing_id = c.owner_id
WHERE
    c.field_id = 11
GROUP BY c.value

Upvotes: 4

Related Questions