CODEWITHSUNDEEP
sql
AZW
AZW

Reputation: 63

Join with count

I need to write SQL query like:

I have tables:

● Table users (id, email, citizenship_country_id)

● Table countries (id, name, iso)

Users with columns: id, email, citizenship_country_id

Countries with columns: id, name, iso

SELECT countries.name, 
       Count(users.citiizenship_country_id) AS W1
FROM countries 
LEFT JOIN users ON countries.id = users.citizenship_country_id
GROUP BY users.citiizenship_country_id, countries.name
HAVING ((([users].[citiizenship_country_id])>2));

But this does not work - I get an empty result set.

Could you please tell me what I'm doing wrong?

Upvotes: 1

Views: 1251

Answers (2)

leftjoin
leftjoin

Reputation: 38290

Group by country name and use HAVING Count(u.citiizenship_country_id)>1000, it filters rows after aggregation:

SELECT c.name, 
       Count(u.citiizenship_country_id) AS W1
 FROM countries c
      INNER JOIN users u ON c.id = u.citizenship_country_id
GROUP BY c.name
HAVING Count(u.citiizenship_country_id)>1000
ORDER BY W1 desc --Order top counts first
;

As @GordonLinoff pointed, you can use INNER JOIN instead of LEFT JOIN, because anyway this query does not return counries without users and INNER JOIN performs better because no need to pass not joined records to the aggregation.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269573

A LEFT JOIN is superfluous for this purpose. To have 1000 users, you need at least one match:

SELECT c.name, Count(*) AS W1
FROM countries c JOIN
     users u
     ON c.id = u.citizenship_country_id
GROUP BY c.name
HAVING COUNT(*) > 1000;

Notice that table aliases also make the query easier to write and to read.

Upvotes: 2

Related Questions