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
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:
NOTE: The expression
t.desired_shape = 'fade'
is equivalent toor the more ANSI compliant