1

I'm trying to calculate the number of sales associated with each of my sellers. I've managed to do this for overall sales by joining to a subquery as below:

SELECT seller.*, COALESCE(apc,0) AS total_sales
FROM seller
LEFT JOIN (
    SELECT seller_id, COUNT(seller_id) AS apc
    FROM purchase
    GROUP BY seller_id
) AS ap ON ap.seller_id = seller.id

However, I also want to calculate the number of sales over the last three months, so I extended the query to this:

SELECT seller.*, COALESCE(apc,0) AS total_sales, COALESCE(rpc,0) AS recent_sales
FROM seller
LEFT JOIN (
    SELECT seller_id, COUNT(seller_id) AS apc
    FROM purchase
    GROUP BY seller_id
) AS ap ON ap.seller_id = seller.id
LEFT JOIN (
    SELECT seller_id, COUNT(seller_id) AS rpc
    FROM purchase
    GROUP BY seller_id
    WHERE purchase.time_created > [date three months ago]
) AS rp ON rp.seller_id = seller.id

When I run this query, I get the error Unknown column 'rpc' which I believe is happening because the rp subquery is coming back empty as my current data set has no sales in the past three months.

I've used the COALESCE function to handle a null value in a field, but this second subquery doesn't even seem to create a table to contain any null fields.

Is there a way to set some default values for this?

Stuart Kemp
  • 150
  • 1
  • 11
  • You may case when in mysql, Some thing like this http://stackoverflow.com/questions/9588015/how-do-i-use-properly-case-when-in-mysql – Selvakumar Ponnusamy May 06 '16 at 08:14
  • @SelvakumarPonnusamy I'm not sure how that would help me. What would my case condition be to check if the subquery had any records? – Stuart Kemp May 06 '16 at 08:16

0 Answers0