CASE WHEN
Aggregates can have an internal CASE WHEN
condition.
Application: use multiple aggregates in a single query: http://stackoverflow.com/questions/5462961/how-to-combine-two-count-queries-to-their-ratio
Example: calculate the total net upvotes minus downvotes:
CREATE TABLE votes (
article_id INT,
type CHAR(4)
);
INSERT INTO votes VALUES
(1, 'UP'), (1, 'DOWN'), (2, 'UP'), (3, 'DOWN');
SELECT
article_id,
SUM(
CASE type
WHEN 'UP' THEN 1
WHEN 'DOWN' THEN -1
END
) AS count
FROM votes
GROUP BY article_id
ORDER BY count DESC;
DROP TABLE votes;
This example could be simplified by using type as INT
and upvote as 1
and downvote as -1
, then we can just do a SUM(type)
.
MySQL extension
It is possible to write things like:
COUNT(column = 0)
SUM(column > 0)
in MySQL. The more portable SQL equivalent is to use CASE WHEN
with null
for COUNT
and 0
for SUM
.