CODEWITHSUNDEEP
mysqlsql
Dylan McCann
Dylan McCann

Reputation: 113

Pivoting rows with aggregates other than SUM?

Is it possible to pivot rows in MySQL, with aggregates other than SUM? I've been looking online, and through my courses book. But, I cannot find anything.

Upvotes: 1

Views: 366

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270697

Short answer - yes you can pivot use other aggregates depending on the type of query you need to perform - each can serve a specialized purpose.

Using COUNT() to count up rows (like SUM())

Because aggregate functions eliminate NULL values, you will sometimes encounter a pattern similar to the SUM(CASE...) you are probably accustomed to seeing (whose expression returns a 1 or 0, which get added up). Instead of using a SUM() aggregate in this method, you can use a COUNT() instead, provided its expression returns a value (any value) or a NULL. The NULLs get eliminated, and the COUNT() counts up the returned non-null rows. This is only useful for counting up values though

Here's an example counting up rows per group where val = 1, using a COUNT() aggregate. Suppose you have the following table and you want the results as columns a, b.

group value
 a     1
 a     1
 a     2
 b     1
 b     2

Using COUNT()

SELECT 
  `group`,
  /* the aggregate eliminates NULLs (rows where val <> 1) */
  COUNT(CASE WHEN `group` = 'a' THEN val ELSE NULL END) AS `a`,
  COUNT(CASE WHEN `group` = 'b' THEN val ELSE NULL END) AS `b`
FROM pivot
WHERE val = 1

Result:

 a      b
-------------
 2      1

Demonstration: http://sqlfiddle.com/#!2/b4585/7

That is basically the same as can be done with SUM() and zeros and ones:

Using SUM() like you've probably seen accomplishes the same thing:

SELECT 
  /* the aggregate eliminates NULLs (rows where val <> 1) */
  SUM(CASE WHEN `group` = 'a' THEN 1 ELSE 0 END) AS `a`,
  SUM(CASE WHEN `group` = 'b' THEN 1 ELSE 0 END) AS `b`
FROM pivot
WHERE val = 1

Demonstration: http://sqlfiddle.com/#!2/b4585/8

Using MAX() to pivot a list of key/value pairs:

Occasionally, you just want to change a 1-to-1 mapping from rows to columns. This is useful if you needed to flatten out a list of key/value pairs for example. Suppose you had a table like this, where there will only ever be one row per column you want to pivot out:

key    value
------------
k1     v1
k2     v2
k3     v3

... and you want a result like this:

k1  k2  k3
-----------
v1  v2  v3

In this case, you can use a MAX() or MIN() aggregate, because again since the NULL values will get eliminated by the aggregate, only the one that actually has a non-null will be returned, and hence be the MAX().

So whi2le this query would pivot them but result in 3 rows, with only one non-null per row:

SELECT
  CASE WHEN `key` = 'k1' THEN `value` ELSE NULL END AS `k1`,
  CASE WHEN `key` = 'k2' THEN `value` ELSE NULL END AS `k2`,
  CASE WHEN `key` = 'k3' THEN `value` ELSE NULL END AS `k3`
FROM key_value

Result:

k1   k2   k3
---------------
v1   NULL NULL
NULL v2   NULL
NULL NULL v3

Demonstration: http://sqlfiddle.com/#!2/813b3/5

By using a MAX() aggregate, you effectively collapse it down to one row by getting rid of the NULLs

SELECT
  MAX(CASE WHEN `key` = 'k1' THEN `value` ELSE NULL END) AS `k1`,
  MAX(CASE WHEN `key` = 'k2' THEN `value` ELSE NULL END) AS `k2`,
  MAX(CASE WHEN `key` = 'k3' THEN `value` ELSE NULL END) AS `k3`
FROM key_value

Gives the result we wanted - one row pivoted out:

k1  k2  k3
-----------
v1  v2  v3

Demonstration: http://sqlfiddle.com/#!2/813b3/4

Upvotes: 2

Related Questions