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

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.


SELECT 

COALESCE(t1.amount,0) AS link_earnings, 
COALESCE(t2.amount,0) AS publisher_referral_earnings, 
COALESCE(t3.amount,0) AS advertiser_referral_earnings, 
t1.day AS day

FROM

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

LEFT JOIN

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

ON t1.day = t2.day

LEFT JOIN

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

ON t1.day = t3.day

ORDER BY day

Seems to run ok....


You can simply use an outer join to retain earnings even when there is no matching referral, and then conditionally sum depending on whether a referral exists or not:

SELECT   DATE_FORMAT(e.earning_created, '%c/%e/%Y') AS day,
         SUM(IF(r.referral_id IS NULL, e.earning_amount, 0)) earning_standard,
         SUM(IF(r.referral_id IS NULL, 0, e.earning_amount)) earning_referral
FROM     earnings e LEFT JOIN referrals r ON r.referral_id = e.earning_referral_id
WHERE    e.earning_account_id = ?
     AND e.earning_created > CURRENT_DATE - INTERVAL 90 DAY
     AND (r.referral_id IS NULL OR r.referral_type = 0)
GROUP BY 1
ORDER BY 1

I've assumed here that earnings.earning_referral_id is never negative, though you can add an explicit test to filter such records if so desired.

I've also changed the filter on earnings.earning_created to base from CURRENT_DATE rather than NOW() to ensure that any earnings created earlier than the current time on the first day of the series are still included—this would typically be what one actually wants, but feel free to change back if not.

Tags:

Mysql