MySql graph query multiple series aligned to same time x-axis

Question

I have queries that I'm using to make a graph of earnings. But now people are able to earn from two different sources, so I want to separate this out into two lines on the same chart

This one for standard earnings:

SELECT DATE_FORMAT(earning_created, '%c/%e/%Y') AS day, SUM(earning_amount) AS earning_standard
FROM earnings
WHERE earning_account_id = ? AND earning_referral_id = 0 AND (earning_created > DATE_SUB(now(), INTERVAL 90 DAY))
GROUP BY DATE(earning_created)
ORDER BY earning_created

And this one for referral earnings:

SELECT DATE_FORMAT(e.earning_created, '%c/%e/%Y') AS day, SUM(e.earning_amount) AS earning_referral
FROM earnings AS e
INNER JOIN referrals AS r
ON r.referral_id = e.earning_referral_id
WHERE e.earning_account_id = ? AND e.earning_referral_id > 0 AND (e.earning_created > DATE_SUB(now(), INTERVAL 90 DAY)) AND r.referral_type = 0
GROUP BY DATE(e.earning_created)
ORDER BY e.earning_created

How do I get it to run the queries together, so that it outputs two columns/series for the y-axis: earning_standard and earning_referral.

But with them both aligned to the same day column/scale for the x-axis - substituting zero when there are no earnings for a specific series.

Answer 1

You'll need to set both of those queries as subqueries

SELECT DATE_FORMAT(earnings.earning_created, '%c/%e/%Y') AS day, 
       COALESCE(es.earning_standard, 0) AS earning_standard, 
       COALESCE(er.earning_referral, 0) AS earning_referral
FROM earnings
LEFT JOIN (SELECT DATE_FORMAT(earning_created, '%c/%e/%Y') AS day,   
                  SUM(earning_amount) AS earning_standard
           FROM earnings
           WHERE earning_account_id = ? 
           AND earning_referral_id = 0 
           AND (earning_created > DATE_SUB(now(), INTERVAL 90 DAY))
           GROUP BY DATE(earning_created)) AS es 
ON (day = es.day)
LEFT JOIN (SELECT DATE_FORMAT(e.earning_created, '%c/%e/%Y') AS day,    
                  SUM(e.earning_amount) AS earning_referral
           FROM earnings AS e
           INNER JOIN referrals AS r
           ON r.referral_id = e.earning_referral_id
           WHERE e.earning_account_id = ? 
           AND e.earning_referral_id > 0 
           AND (e.earning_created > DATE_SUB(now(), INTERVAL 90 DAY)) 
           AND r.referral_type = 0
           GROUP BY DATE(e.earning_created)) AS er 
ON (day = er.day)
WHERE earnings.earning_account_id = ?
ORDER BY day

where I'm assuming earning_account_id = ? is intended to be with a question mark because the language you're using to run the query is replacing it with the actual id before running the query.