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?