Skip to content

Instantly share code, notes, and snippets.

@kuroisuna
Last active September 17, 2016 04:04
Show Gist options
  • Save kuroisuna/a933012fb26e49208923b51a43733fd5 to your computer and use it in GitHub Desktop.
Save kuroisuna/a933012fb26e49208923b51a43733fd5 to your computer and use it in GitHub Desktop.
SQL: Transposing rows without subqueries
-- Say we have this table called selected_colors:
-- +----------------+
-- | type | value |
-- +--------+-------+
-- | blue | 1 |
-- | red | 4 |
-- | yellow | 8 |
-- | red | 12 |
-- | blue | 5 |
-- | blue | 0 |
-- +--------+-------+
-- And we want this object:
-- [blue] = 6, [red] = 16, [yellow] = 8
SELECT
SUM(CASE sc.type WHEN 'blue' THEN sc.value ELSE 0 END) AS blue,
SUM(CASE sc.type WHEN 'red' THEN sc.value ELSE 0 END) AS red,
SUM(CASE sc.type WHEN 'yellow' THEN sc.value ELSE 0 END) AS yellow
FROM selected_colors AS sc;
-- Result:
-- ======
-- +------+-----+--------+
-- | blue | red | yellow |
-- +------+-----+--------+
-- | 6 | 16 | 8 |
-- +------+-----+--------+
-- Why? How?
-- Is really simple in fact, lets add a GROUP BY value (works in this
-- certain case because every value is distinct)
-- Original table: Result:
-- ============== ======
-- +----------------+ +------+-----+--------+
-- | type | value | | blue | red | yellow |
-- +--------+-------+ +------+-----+--------+
-- | blue | 1 | | 1 | 0 | 0 |
-- | red | 4 | | 0 | 4 | 0 |
-- | yellow | 8 | | 0 | 0 | 8 |
-- | red | 12 | | 0 | 12 | 0 |
-- | blue | 5 | | 5 | 0 | 0 |
-- | blue | 0 | | 0 | 0 | 0 |
-- +--------+-------+ +------+-----+--------+
-- Then the SUM does its works and voilá
-- In JSON: {"blue":6,"red":16,"yellow":8}
-- And what about COUNT? The same case, just replace SUM
SELECT
COUNT(CASE sc.type WHEN 'blue' THEN sc.value ELSE 0 END) AS blue_count,
COUNT(CASE sc.type WHEN 'red' THEN sc.value ELSE 0 END) AS red_count,
COUNT(CASE sc.type WHEN 'yellow' THEN sc.value ELSE 0 END) AS yellow_count
FROM selected_colors AS sc;
-- And what about AVG? We would need to replace 0 with NULL
-- because in an average a zero represents a value:
-- AVG(5, 0, 5) = 5 + 0 + 5 / 3 = 3.33
-- AVG(5, NULL, 5) = 5 + 5 / 2 = 5
SELECT
COUNT(CASE sc.type WHEN 'blue' THEN sc.value ELSE NULL END) AS blue_avg,
COUNT(CASE sc.type WHEN 'red' THEN sc.value ELSE NULL END) AS red_avg,
COUNT(CASE sc.type WHEN 'yellow' THEN sc.value ELSE NULL END) AS yellow_avg
FROM selected_colors AS sc;
-- And what if we need other criteria or more operations?
-- Just modify the CASE
SELECT
COUNT(CASE WHEN sc.type = 'blue' AND sc.type > 0 THEN THEN sc.value * 2 ELSE NULL END) AS blue,
COUNT(CASE WHEN sc.type = 'red' AND sc.type > 0 THEN THEN sc.value * 2 ELSE NULL END) AS red,
COUNT(CASE WHEN sc.type = 'yellow' AND sc.type > 0 THEN THEN sc.value * 2 ELSE NULL END) AS yellow
FROM selected_colors AS sc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment