CODEWITHSUNDEEP
mysqlcountcase
John James
John James

Reputation: 657

SELECT count with condition inside CASE

I am trying to count certain rows of a table when a condition is met. Below is an example of what am trying to do.

SELECT (CASE
        WHEN COUNT(*)<9 WHERE (tb1.col IS NULL OR tb1.col=1) THEN 1
        WHEN COUNT(*)<9 WHERE tb1.col=2 THEN 2
        WHEN COUNT(*)<9 WHERE tb1.col=3 THEN 3
        WHEN COUNT(*)<9 WHERE tb1.col=4 THEN 4
        WHEN COUNT(*)<9 WHERE tb1.col=5 THEN 5
        WHEN COUNT(*)<9 WHERE tb1.col=6 THEN 6
    END)
WHERE tb1.id=X

I know its not exactly working like that, but I was wondering, since I have to make 12 more WHEN.. THEN, if there is another way to do it. Basically am trying to count rows that meet a condition and check if there are less than 9. Repeat that 17 more times.

Upvotes: 0

Views: 117

Answers (1)

Barmar
Barmar

Reputation: 781028

Group your data by tb1.col and get the counts of each group. Then find the first value that has less than 9.

SELECT MIN(col) AS val
FROM (
    SELECT IFNULL(tb1.col, 1) AS col, COUNT(*) AS count
    FROM yourTable AS tb1
    WHERE tb1.id = X
    GROUP BY col
) x
WHERE count < 9

Upvotes: 1

Related Questions