fluffys fluffys - 1 month ago 5
MySQL Question

Selecting count of multiple columns but only include the count if a columns value is more than 0

Not sure if I explained the issue correctly in the title, but basically I have this table which saves a players stage times:

CREATE TABLE `stagetimes`
(
`steamid` VARCHAR(32) CHARACTER SET utf8 NOT NULL DEFAULT '',
`name` VARCHAR(32) CHARACTER SET utf8 DEFAULT NULL,
`mapname` VARCHAR(32) CHARACTER SET utf8 NOT NULL DEFAULT '',
`s1` FLOAT DEFAULT '-1',
`s2` FLOAT DEFAULT '-1',
`s3` FLOAT DEFAULT '-1',
`s4` FLOAT DEFAULT '-1',
`s5` FLOAT DEFAULT '-1'
)
engine=innodb
ALTER TABLE `stagetimes`
ADD PRIMARY KEY (`steamid`,`mapname`);


This table actually goes up to s35 but to keep it simple, I left the rest out.

As soon as someone completes stage 1, a new row gets inserted with their steamid, their name, the map name and their stage 1 time with the rest of columns being -1 and being updated once they beat the additional stages, however, not every map has 5 stages, some may only have 4 or even less, so obviously if the columns value for a player is -1, the stage either doesn't exist or they haven't finished the stage therefore not updating the time from -1.

I want to select the total amount of stages that a specific player has completed as a count basically; I made up a query which works but it is extremely long and I was wondering if there was another way to achieve this:

SELECT Sum(stages)
FROM ((SELECT Count(`s1`) AS `stages`
FROM `stagetimes`
WHERE `steamid` = 'STEAM_1:0:00000000'
AND `s1` > 0)
UNION ALL
(SELECT Count(`s2`) AS `stages`
FROM `stagetimes`
WHERE `steamid` = 'STEAM_1:0:00000000'
AND `s2` > 0)
UNION ALL
(SELECT Count(`s3`) AS `stages`
FROM `stagetimes`
WHERE `steamid` = 'STEAM_1:0:00000000'
AND `s3` > 0)
UNION ALL
(SELECT Count(`s4`) AS `stages`
FROM `stagetimes`
WHERE `steamid` = 'STEAM_1:0:00000000'
AND `s4` > 0)
UNION ALL
(SELECT Count(`s5`) AS `stages`
FROM `stagetimes`
WHERE `steamid` = 'STEAM_1:0:00000000'
AND `s5` > 0)) t1


I don't really want to be doing this 35 times if it can be avoided.

Sample Data:

+-------------------+------------+---------------+--------+--------+--------+--------+--------+
| steamid | name | mapname | s1 | s2 | s3 | s4 | s5 |
+-------------------+------------+---------------+--------+--------+--------+--------+--------+
|STEAM_1:0:20019070 | fluffys | surf_catrix | 13.562 | 12.593 | 14.328 | 21.984 | -1 |
|STEAM_1:0:20019070 | fluffys | surf_beginner | 2.753 | 9.185 | 14.406 | 23.359 | 11.343 |
+-------------------+------------+---------------+--------+--------+--------+--------+--------+


From this data, the query should return 9 as there are 9 stages that don't have the value of -1

Answer

Use SUM() rather than COUNT() to put it into a single query. Conditional expressions return 1 when they're true, 0 when they're false, so you can simply add them.

SELECT SUM((s1 > 0) + (s2 > 0) + (s3 > 0) + (s4 > 0) + (s5 > 0)) AS total_stages
FROM stagetimes
WHERE steamid = 'STEAM_1:0:00000000'

It would probably be better to reorganize your schema. Instead of having each stage as a column in the table, have a separate row for each stage, with the stage number as a column. Then you would only have rows for the stages that actually exist.

Comments