MySQL count identical values

Question

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?)

score 3 · Accepted Answer · 2015-04-25 11:12:46Z

3

Something like:

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

edited Apr 25, 2015 at 11:12

answered Apr 25, 2015 at 9:18

user2067248

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2015-04-25 10:44:05Z

2

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`

edited Apr 25, 2015 at 10:44

answered Apr 25, 2015 at 9:17

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Collectives™ on Stack Overflow

MySQL count identical values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related