CODEWITHSUNDEEP
mysqlsql
Jeff Gortmaker
Jeff Gortmaker

Reputation: 4737

SQL: Calculate percentage in other table, and user percentage to filter?

I have 2 tables. The first one, called 'users' looks like this:

id | Username
----------------------
1  | time
2  | bill
3  | jeff

The second one, called 'posts' looks like this:

id | user | category
----------------------
1  | bill | 3
2  | tim  | 1
3  | bill | 3
4  | bill | 2

Each different number in the categories column corresponds to a category.

I'm attempting to create a SQL query that will get all the users who have more than 10% of their posts in a specific category. Here's what my current (not working) code looks like for category 3:

SELECT 
    u.Username, 
    (
        (SELECT COUNT(*) FROM posts WHERE user=u.Username AND category=3) 
        / (SELECT COUNT(*) FROM posts WHERE user=u.Username) 
        * 100
    ) AS percentage 
FROM users u 
WHERE percentage > 10

This query should in theory return 'bill'. However, it doesn't work! Do I have a syntax error somewhere, or is my method bad/wrong?

Upvotes: 0

Views: 2891

Answers (2)

The Scrum Meister
The Scrum Meister

Reputation: 30111

Instead of using all those subqueries, you can query the posts table directly:

SELECT Username,
    (SUM(Category = 3) / COUNT(*)) * 100 AS percentage 
FROM posts
GROUP BY 1
HAVING percentage > 10

Upvotes: 2

Nicola Cossu
Nicola Cossu

Reputation: 56377

Percentage alias can't exist within where clause. You have to use having clause.

SELECT 
    u.Username, 
    (
        (SELECT COUNT(*) FROM posts WHERE user=u.Username AND category=3) 
        / (SELECT COUNT(*) FROM posts WHERE user=u.Username) 
        * 100
    ) AS percentage 
FROM users u 
HAVING percentage > 10

Upvotes: 1

Related Questions