CODEWITHSUNDEEP
mysqlsqlselectcount
Micael Dias
Micael Dias

Reputation: 341

MySQL count identical values

I have this table named prizes with the following structure

`id` (PRIMARY)
`id_multiple`
`desc`
`winner`

I want to select those who don't have a winner (NULL) and display them together if they have the same id_multiple showing the count of how many left to win of that id_multiple.

So for example, there's this values:

id_multiple | winner | desc
1           | NULL   | voucher
1           | jonh   | voucher
2           | NULL   | car
2           | NULL   | car

And I want to display:

Left to win:
1 Voucher
2 Car

(The desc will be the same for all id_multiple so it might be ambiguous to use id_multiple?)

Upvotes: 1

Views: 67

Answers (2)

user2067248
user2067248

Reputation:

Something like:

SELECT id_multiple,count(id_multiple),`desc` 
FROM `yourtable`
WHERE `winner` IS NULL
GROUP BY `id_multiple`

Upvotes: 3

Mureinik
Mureinik

Reputation: 311188

You could count a case expression:

SELECT   id_multiple, COUNT(CASE WHEN winner IS NULL THEN 1 END) AS left_to_win, `desc`
FROM     mytable
GROUP BY id_multiple, `desc`

Or, even simpler, with a sum expression that takes advantage of the fact that true is interpreted as 1 and false as 0 in numerical contexts:

SELECT   id_multiple, SUM(winner IS NULL) AS left_to_win, `desc`
FROM     mytable
GROUP BY id_multiple, `desc`

Upvotes: 2

Related Questions