CODEWITHSUNDEEP
mysql
bcmcfc
bcmcfc

Reputation: 26765

How do I add up multiple SUMs from different subqueries into one result?

How can I add up SUMs from different subqueries in MySQL?

JOIN ( SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes, customer_id FROM product1_quote GROUP BY customer_id ) p1q ON (p1q.customer_id = c.customer_id)

JOIN ( SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes, customer_id FROM product2_quote GROUP BY customer_id ) p1q ON (p1q.customer_id = c.customer_id)

So I'd want to add those two up and have numQuotes be the total numQuotes. However, it's a little more complicated than that, because the number of different tables is dynamic, so in any given circumstance there could be any number of subqueries.

Upvotes: 0

Views: 402

Answers (2)

bcmcfc
bcmcfc

Reputation: 26765

I've solved it by changing the JOINs to LEFT JOINs and using IFNULL(numQuotes".$k.",0)+ inside the PHP loop that puts the queries together, where $k is an index.

So the end result is something like:

SELECT IFNULL(numQuotes0,0)+IFNULL(numQuotes1,0) AS totalQuotes FROM ...

LEFT JOIN ( SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes0, customer_id FROM product1_quote GROUP BY customer_id ) p1q ON (p1q.customer_id = c.customer_id) LEFT JOIN ( SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes1, customer_id FROM product2_quote GROUP BY customer_id ) p2q ON (p2q.customer_id = c.customer_id)

The LEFT JOINs return NULL if no results are found, hence the need for IFNULL.

Upvotes: 0

pkauko
pkauko

Reputation: 492

What comes up with the following?

select sum(numQuotes), customer_id from
(
  (SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes, customer_id FROM 
  product1_quote GROUP BY customer_id ) p1q ON (p1q.customer_id = c.customer_id)
UNION
  (SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes, customer_id FROM 
  product2_quote GROUP BY customer_id ) p1q ON (p1q.customer_id = c.customer_id)
) group by customer_id;

Parentheses might be off so check them first.

Upvotes: 1

Related Questions