How to count rows on joined table without WHERE

Question

I have two related tables users and networks — social-media accounts of corresponding user. Accounts (networks) could be like these: facebook, linkedin, youtube, vimeo, etc...

Every user could have zero or more social-media accounts (in networks table).

In my case I need to count only video accounts (youtube and/or vimeo) of each user.

I'm trying this two queries:

SELECT users.email, COUNT(networks.network) as video_networks_counter,
FROM users
LEFT JOIN networks ON networks.user_id = users.id
WHERE networks.network='youtube' OR networks.network='video'
GROUP BY users.id

and

SELECT users.email, 
CASE networks.network WHEN 'youtube' THEN 1 ELSE 0 END as video_networks_counter,
CASE networks.network WHEN 'vimeo' THEN 1 ELSE 0 END as video_networks_counter
FROM users
LEFT JOIN networks ON networks.user_id = users.id
GROUP BY users.id

Which don't work as desired.

Problem with first query is that user could not have any networks but he also have to be in a list with 0 counter. So WHERE condition seems to be useless in my case.

Second query with CASE also doesn't work.

Equality condition on `WHERE` clause that refers to second table with `LEFT OUTER JOIN` makes it literally a `INNER JOIN` — Lukasz Szozda, Sep 07 '19 at 17:04
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Sep 07 '19 at 17:44
This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings, names & line numbers & with & without 'site:stackoverflow.com' and read many hits & answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Sep 07 '19 at 17:44
Please in code questions give a [mre]--cut & paste & runnable code; example input (as tabular initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS "doesn't work" doesn't tell us anything. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. Please ask one question per post. — philipxy, Sep 07 '19 at 17:45

score 1 · Answer 1 · answered Sep 07 '19 at 17:03

In your 1st query set the conditions in the ON clause and remove WHERE:

SELECT users.email, COUNT(networks.network) as video_networks_counter,
FROM users
LEFT JOIN networks ON networks.user_id = users.id
AND (networks.network='youtube' OR networks.network='video')
GROUP BY users.id

Your WHERE clause removes all the users that do not have an account making the LEFT JOIN useless.

score 1 · Accepted Answer · answered Sep 07 '19 at 17:04

Move the conditions to the on clause:

SELECT u.email, COUNT(n.network) as video_networks_counter
FROM users u LEFT JOIN
     networks n
     ON n.user_id = u.id AND
        n.network IN ('youtube', 'video')
GROUP BY u.email;

Notes:

Your WHERE clause turns the outer join into an inner join, because NULL values do not match.
IN is simpler than OR clauses for this type of comparison.
The GROUP BY key should match the unaggregated columns in the SELECT.
Table aliases make the query easier to write and to read.
You may intend 'vimeo' rather than 'video'.

And, if you want to count them separately:

SELECT u.email, COUNT(n.network) as video_networks_counter,
       SUM( n.network = 'youtube') as num_youtube,
       SUM( n.network = 'vimeo') as num_vimeo           
FROM users u LEFT JOIN
     networks n
     ON n.user_id = u.id AND
        n.network IN ('youtube', 'vimeo')
GROUP BY u.email;

score 0 · Answer 3 · answered Sep 07 '19 at 17:04

0

You could just sum up how often your condition is true

SELECT u.email, 
       SUM(n.network IN ('youtube', 'video')) as video_networks_counter,
FROM users u
LEFT JOIN networks n ON n.user_id = u.id
GROUP BY u.email

answered Sep 07 '19 at 17:04

juergen d

201,996
37
293
362

score 0 · Answer 4 · answered Sep 07 '19 at 17:05

You can try this option with CASE and SUM-

SELECT users.email, 
SUM(
    CASE 
        WHEN networks.network='youtube' OR networks.network='video' THEN 1 
        ELSE 0 
    END
) as video_networks_counter,
FROM users
LEFT JOIN networks ON networks.user_id = users.id
GROUP BY users.email

How to count rows on joined table without WHERE

4 Answers4