CODEWITHSUNDEEP
mysqlaggregate-functions
Kristian
Kristian

Reputation: 21830

MySQL query for aggregating terms over several conditions

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:

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

Upvotes: 0

Views: 24

Answers (1)

spencer7593
spencer7593

Reputation: 108450

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

Upvotes: 1

Related Questions