MYSQL select query using count (*)

Question

i have a problem concerning a select query in MYSQL

i have two different tables and i want to obtain a certain result

i used COUNT method which gave me only the results (>=1)

But in reality , i want to use all counts with zero included how to do it?

My query is:

SELECT 
    first.subscriber_id, 
    second.tag_id,
    COUNT(*)
FROM 
    content_hits first  
    JOIN content_tag second ON first.content_id=second.content_id 
GROUP BY  
    second.Tag_id,first.Subscriber_id<br>

First table:Content_hits

CONTENT_ID  SUBSCRIBER_ID   
30          1   
10          10  
34          4   
32          2   
40          3 
28          3   
30          6   
31          8   
12          3

Second table:Content_tag

CONTENT_ID   TAG_ID
1            1
2            1
3            1
4            1
5            1
6            1
7            1
8            1
9            1
10           1
11           2
12           2
13           2
14           2

Result but incomplete For example:Subsrciber6 for tag_id=1 should have a count(*)=0

subscriber_id   tag_id   COUNT(*)
1               1        4
2               1        7
3               1        2
4               1        1
5               1        3
7               1        2
8               1        1
9               1        1
10              1        3
1               2        2
2               2        3
3               2        2

If you definitely will have a record in content_hits but not necessarily in content_tag then change the join to a left join which will return all entries in content_hits whether or not there's an entry in content_tag — kaj, Mar 29 '12 at 11:34

Lukas Eder · Accepted Answer · 2012-03-30T12:49:47.920

2

Now that you have further elaborated on what you actually want to achieve, it can be seen that the problem is much more complex. You actually want all combinations of subscriber_id and tag_id, and then count the number of actual entries in the joined table product. whew. So here goes the SQL:

SELECT combinations.tag_id,
       combinations.subscriber_id,

-- correlated subquery to count the actual hits by tag/subscriber when joining
-- the two tables using content_id

       (SELECT count(*)
        FROM content_hits AS h
        JOIN content_tag AS t ON h.content_id = t.content_id
        WHERE h.subscriber_id = combinations.subscriber_id
        AND t.tag_id = combinations.tag_id) as cnt

-- Create all combinations of tag/subscribers first, before counting anything
-- This will be necessary to have "zero-counts" for any combination of
-- tag/subscriber

FROM (
  SELECT DISTINCT tag_id, subscriber_id
  FROM content_tag
  CROSS JOIN content_hits
) AS combinations

edited Mar 30 '12 at 12:49

answered Mar 29 '12 at 11:33

Lukas Eder

211,314
129
689
1,509

in fact,the content_id is the id of a content belonging to a tag_id.But each subscriber could have for a certain tag a zero count assuming that he doesn't have a content_id belonging to this tag. – holy Mar 29 '12 at 11:50
@holy: Then I think I correctly understood the intent of your query. Try my suggestion... – Lukas Eder Mar 29 '12 at 11:52
not working :( i have tried to split the two members of the query : SELECT first.subscriber_id, second.tag_id, COUNT( * ) FROM content_hits first LEFT OUTER JOIN content_tag second ON first.content_id = second.content_id GROUP BY second.Tag_id, first.Subscriber_id. But the result is:1 1 4 2 1 7 3 1 2 4 1 1 5 1 3 7 1 2 8 1 1 9 1 1 10 1 3 Subsriber6 should figure in the result: 6 1 0 (6:nb of sub 1:tag_id 0:count(*)) – holy Mar 29 '12 at 12:05
Do you want to count the number of tags per subscriber, or the number of subscribers per tag? Or do you want to count the number of subscribers and tags per content_id? Because the latter is an entirely different query...! – Lukas Eder Mar 29 '12 at 12:16
i want to count the number of content_id for each subscriber and order them by tag (because many content_id belong to one tag) – holy Mar 29 '12 at 12:25
@holy: Maybe you should re-phrase your question, then. Show your entities, show the result you expect. Right now, this may be a bit confusing for future readers... – Lukas Eder Mar 29 '12 at 12:46
@holy: Thanks for your update. See my corrected answer. I think this is what you need, now – Lukas Eder Mar 30 '12 at 12:50

score 0 · Answer 2 · answered Mar 29 '12 at 11:32

0

Not sure, but is this what you want?

 SELECT first.subscriber_id, second.tag_id, COUNT(*) AS c 
 FROM content_hits first JOIN content_tag second ON first.content_id=second.content_id 
 GROUP BY second.Tag_id,first.Subscriber_id HAVING c = 0

answered Mar 29 '12 at 11:32

dotoree

2,983
1
24
29

MYSQL select query using count (*)

2 Answers2

Linked