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
– kaj, Commented Mar 29, 2012 at 11:34

Lukas Eder · Accepted Answer · 2012-03-30 12:49:47Z

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, 2012 at 12:49

answered Mar 29, 2012 at 11:33

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

holy Over a year ago

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.

Lukas Eder Over a year ago

@holy: Then I think I correctly understood the intent of your query. Try my suggestion...

holy Over a year ago

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(*))

Lukas Eder Over a year ago

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...!

holy Over a year ago

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)

|

dotoree · Accepted Answer · 2012-03-29 11:32:31Z

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, 2012 at 11:32

dotoree

2,9911 gold badge26 silver badges29 bronze badges

Collectives™ on Stack Overflow

MYSQL select query using count (*)

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related