Reputation: 3461
I'm struggling with the following SQL: I have a table that has a site_id and the referrer domain for a site. I'm trying to simply count occurrences by referrer and site and then calculate the total of all referrers for one site.
In my results below the count and site total are incorrect. The count should be half, e.g. for site id 1 the count for google and amazon should be 10 and the total for site id 2 should be 18. Any ideas what I could be doing wrong?
SELECT site_id, I.referrer_domain AS referrer_domain, COUNT(*) AS items, t.site_total
FROM qVisitor_Tracking_1 as I,
(SELECT COUNT(1) AS site_total
FROM `qVisitor_Tracking_1`
WHERE `referrer_domain` != '' group by site_id) AS T
WHERE `referrer_domain` != ''
GROUP BY `site_id`, referrer_domain
site_id referrer count site total
1 amazon 20 24
1 google 20 24
2 amazon 12 24
2 google 18 24
Upvotes: 0
Views: 121
Reputation: 445
In mysql you can use the GROUP BY
modifier WITH ROLLUP
:
SELECT site_id, referrer_domain COUNT(*) AS items
FROM qVisitor_Tracking_1
WHERE referrer_domain != ''
GROUP BY site_id, referrer_domain WITH ROLLUP
it should give a result like this:
site_id referrer items
1 amazon 7
1 google 13
1 NULL 20
2 amazon 6
2 google 9
2 NULL 15
NULL NULL 35
see: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
Upvotes: 1
Reputation: 57421
You don't have JOIN between I and T neither direct nor indirect (in where section). So yoou have just multiplication
FROM qVisitor_Tracking_1 as I,
(SELECT COUNT(1) AS site_total
FROM `qVisitor_Tracking_1`
WHERE `referrer_domain` != '' group by site_id) AS T
WHERE `referrer_domain` != ''
Upvotes: 0
Reputation: 21533
If I understand your requirements it looks like you are missing the ON clause to join the sub query to the main table.
SELECT I.site_id,
I.referrer_domain AS referrer_domain,
COUNT(*) AS items,
T.site_total
FROM qVisitor_Tracking_1 as I
INNER JOIN
(
SELECT site_id,
COUNT(1) AS site_total
FROM qVisitor_Tracking_1
WHERE referrer_domain != ''
GROUP BY site_id
) AS T
ON I.site_id = T.site_id
WHERE referrer_domain != ''
GROUP BY I.site_id, referrer_domain, T.site_total
Upvotes: 2