CODEWITHSUNDEEP
mysqlsqlcount
Marc Alexander
Marc Alexander

Reputation: 821

SQL JOIN COUNT then using that count value in where clause

I'm trying to select if a user rating (user.rating) is greater then 6 or if the user has more then 100 transactions (transaction table count). Basically count how many transactions the user has then where (transaction count >= 100 OR user rating >= 6).

SELECT *
FROM   `user`
JOIN   (SELECT COUNT(*) 
        FROM   transaction 
        WHERE  transaction.user_id=user.id 
        AND type='L' 
        AND status='S') AS tcount 
WHERE  (user.rating >= '6' OR tcount >= '100')

Upvotes: 0

Views: 61

Answers (3)

McNets
McNets

Reputation: 10807

Use an alias on COUNT(*)

SELECT *
FROM   `user`
JOIN   (SELECT user_id, COUNT(*) cnt
        FROM   transaction 
        WHERE type='L' 
        AND status='S'
        GROUP BY user_id) AS tcount 
ON      user.id = tcount.user_id
WHERE  (user.rating >= '6' OR tcount.cnt >= '100')

Upvotes: 2

Junbang Huang
Junbang Huang

Reputation: 1967

Just another possible answer. I've created simplified schemas to test it, please try it and let me know the result.

SELECT * 
FROM user
WHERE user.rating >= 6 OR (SELECT COUNT(*) FROM transaction WHERE user_id = user.id and type = 'L' and status = 'S') >= 100;

Upvotes: 2

Stefano Zanini
Stefano Zanini

Reputation: 5916

You can write that without the subquery, like this

SELECT  u.id
FROM `user` u
JOIN `transaction` t
ON t.user_id=u.id 
WHERE t.type = 'L' AND t.status = 'S'
GROUP BY u.id
HAVING sum(case when u.rating >= 6 then 1 end) > 0 OR count(*) >= 100

Upvotes: 0

Related Questions