samedi 8 août 2015

How to join across three tables while counting from two of them

I have three tables: domains, signups, and tracking.

I currently have a list of all domains and the number of visitors using this query:

SELECT sites.id, sites.domain, count(tracking.domain) as tracked FROM sites
LEFT JOIN tracking ON sites.domain = tracking.domain
GROUP BY sites.domain
ORDER BY sites.id;

Now, I would also like to get the count (per domain) from a new table called signups.

I've gotten as far as this:

SELECT sites.id, sites.domain, count(tracking.domain) as tracked, signers.cc FROM sites
LEFT JOIN tracking ON sites.domain = tracking.domain
LEFT JOIN (
SELECT signups.domain, count(*) as cc FROM signups
GROUP BY signups.domain
) as signers
USING (domain)
ORDER BY sites.id;

But that doesn't seem to be quite right. Any suggestions?

Aucun commentaire:

Enregistrer un commentaire