SQL Group by one column, count entries in another

Question

I'm using a sqlite3 database, with a table like this.

|name   |action     |
-------------------------
|john   |run        |
|jim    |run        |
|john   |run        |
|john   |jump       |
|jim    |jump       |
|jim    |jump       |
|jim    |dive       |

I want to get an output like this

|name   |run    |jump   |dive   |
---------------------------------
|john   |2  |1  |0  |
|jim    |1  |2  |1  |

The closest I've come is with this, but I would like to have a single row like above.

SELECT name, action, COUNT(name)
FROM table
GROUP BY name, action

|name   |action |COUNT(name)    |
|john   |run    |2      |
|john   |jump   |1      |
|jim    |run    |1      |
|jim    |jump   |2      |
|jim    |dive   |1      |

Also, I will need to have some WHERE statements in the query as well.

Am I up in the night thinking this will work?

Answer 1

You can also accomplish what you want by using a sum aggregate and CASE conditions like this:

SELECT name, 
       sum(CASE WHEN action = 'run' THEN 1 END) as run,
       sum(CASE WHEN action = 'jump' THEN 1 END) as jump,
       sum(CASE WHEN action = 'dive' THEN 1 END) as dive
FROM table
GROUP BY name

You will still have to change the query every time additional actions are added.

Answer 2

What you are trying to do is called cross tabulation. Normally this is available as a feature called pivot table in Excel and other spreadsheet softwares.

I have found a blog article which will help you with this using SQL. Check out pivot-table-hack-in-sqlite3-and-mysql