CODEWITHSUNDEEP
sqljoincount
user79950
user79950

Reputation: 259

SQL Combining Counts with Joins

I have three tables:

Messages
messageid | userid | text 
Ex: 1 | 1303 | hey guys

Users
userid | username
Ex:
1303 | trantor
1301 | tranro1
1302 | trantor2

Favorites
messageid | userid
Ex:
1 | 1302
1 | 1301

What I want to do, is display a table that has usernames, and counts the number of times they're messages were favorited a certain number of times. In the example above, I want to query saying "how many messages does each user have that has been liked exactly twice?" and it would show a table that has a row saying 

trantor | 1

A natural extension is to replace exactly twice with "at least 2", "more than 6", etc. Im trying to combine count with joins and find myself confused. And since the tables are large, Im getting counts but not confident that my query is working correctly. I have read this article but am still confused :L

What I have so far: 

SELECT USERS.username, COUNT(FAVORITES.id) FROM USERS INNER JOIN FAVORITES ON FAVORITES.userID=USERS.id WHERE COUNT(FAVORITES.id) > 2;

But I dont think it works.

On S.O. I've found these questions on "correlated subqueries" but am thoroughly confused.

Would it be something like this?

SELECT  USERS.username,
 ,      (       SELECT  COUNT(FAVORTIES.userid)
                FROM    FAVORITES INNER JOIN ON MESSAGES
                WHERE   FAVORITES.messageid = MESSAGES.messageid
        )
FROM    USERS

Upvotes: 1

Views: 3027

Answers (2)

Fabio
Fabio

Reputation: 23490

When you use aggregate function such as COUNT() you will need to use GROUP BY together with HAVING rather than WHERE

SELECT USERS.username, COUNT(FAVORITES.id) 
FROM USERS 
INNER JOIN FAVORITES 
ON FAVORITES.userID=USERS.id 
GROUP BY USERS.username
HAVING COUNT(FAVORITES.id) > 2;

From documentation

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

Upvotes: 0

domdomcodecode
domdomcodecode

Reputation: 2443

There's a couple things you should know with aggregate functions in SQL. First off, you need to do a GROUP BY if you're selecting an aggregate function. Second, any conditions involving aggregate functions are to be used with a HAVING clause rather than a WHERE.

The GROUP BY is to be applied to the column(s) you're selecting alongside any aggregate functions.

Here's a basic structure:

SELECT attribute1, COUNT(attribute2)
FROM someTable
GROUP BY attribute1
HAVING COUNT(attribute2) > 2;

Apply anything else you're using such as JOINS and ORDER BY and what not.

note: There's a certain order these clauses have to be in. Such as ORDER BY goes after HAVING, which comes after GROUP BY and so forth.

If I'm remembering correctly, the order of operations go:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Upvotes: 1

Related Questions