SQL Group by one column, count entries in another

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 diveFROM tableGROUP BY name

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

I don't know SQLLite that well, but I image that you could use subqueries or temp tables. With mssql you could write something like this:

select Name,      (select count(*) from table as t1 where t1.Name = table.Name and t1.Action = 'run') as Run, (select count(*) from table as t1 where t1.Name = table.Name and t1.Action = 'dive') as dive, (select count(*) from table as t1 where t1.Name = table.Name and t1.Action = 'jump') as run from table 

But this would need to be rewritten every time you ad another action type. You should probably add an index to get the speed up on the table. But check the query plan with "real" data first.

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