MySQL query for aggregating terms over several conditions

Question

Starting with data like the following:

clubType   desiredShape   lineDirection
---------------------------------------
driver     straight       left
driver     draw           straight
iron       straight       right
wedge      straight       straight
iron       fade           right
wedge      straight       straight
iron       fade           left
iron       draw           straight

I'd like to write a query that can return:

• count desiredShape = "draw"
• count desiredShape = "straight"
• count desiredShape = "fade"
• count lineDirection = "left"
• count lineDirection = "straight"
• count lineDirection = "right"

per each clubType

So, I tried doing something like this:

SELECT
    clubType,
    (SELECT count(*) FROM shots WHERE desiredShape = "fade") as count_DesiredFade,
    (SELECT count(*) FROM shots WHERE desiredShape = "draw") as count_DesiredDraw,
    (SELECT count(*) FROM shots WHERE desiredShape = "straight") as count_DesiredStraight
    ...
FROM shots
GROUP BY clubType

But its not right. Not sure how to iterate over clubtype and aggregate the other counts, too.

I'd like to end up with something like this:

clubType   desDraw   desFade   desStraight   lineLeft   lineRight   lineRight
-----------------------------------------------------------------------------
driver     3         2         4             3          2           1
iron       4         1         2             4          2           1
wedge      1         3         2             1          0           2

Answer 1

Use a boolean expression to return a 1 (TRUE) or 0 (FALSE) or NULL. Wrap that in a SUM() aggregate, so you get a "count" of the rows where the boolean expression is TRUE.

For example:

SELECT t.clubType
     , SUM(t.desiredShape = 'fade') as count_DesiredFade
     , SUM(t.desiredShape = 'draw') as count_DesiredDraw
     , SUM(t.desiredShape = 'straight') as count_DesiredStraight
     , ...
  FROM shots t
 GROUP BY t.clubType

---

NOTE: The expression t.desired_shape = 'fade' is equivalent to

  IF(t.desired_shape = 'fade',1,IF(t.desired_shape IS NULL,NULL,0))

or the more ANSI compliant

  CASE WHEN t.desired_shape = 'fade' 
         THEN 1
       WHEN t.desired_shape IS NULL
         THEN NULL
       ELSE 0
  END