CODEWITHSUNDEEP
mysqlsql
checkmate711
checkmate711

Reputation: 3461

MySQL: Count and Total for two different groupings

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

Answers (3)

MauriDev
MauriDev

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

StanislavL
StanislavL

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

Kickstart
Kickstart

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

Related Questions