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?

What you are looking for is I think pivot tables. I found this example on msdn — Mridul Kashatria, Commented Jun 26, 2011 at 16:21
Yes, he needs to PIVOT the data, but there isn't an easy way to do it in sqlite, since there isn't a PIVOT or UNPIVOT operator like there is in SQL Server. — Waleed Al-Balooshi, Commented Jun 26, 2011 at 16:34

Waleed Al-Balooshi · Accepted Answer · 2011-06-26 16:32:26Z

3

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.

answered Jun 26, 2011 at 16:32

Waleed Al-Balooshi

6,41624 silver badges22 bronze badges

Add a comment |

Mridul Kashatria · Accepted Answer · 2011-06-26 16:29:26Z

1

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

answered Jun 26, 2011 at 16:29

Mridul Kashatria

4,1972 gold badges20 silver badges15 bronze badges

Hey mridkash, this is exactly what I was wanting, and thanks for the links and information (teach a man to fish and all that). Testing shows that this executes faster as well.
– valentine
Commented Jun 28, 2011 at 17:49

Add a comment |

Richard L · Accepted Answer · 2011-06-26 16:18:08Z

1

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.

answered Jun 26, 2011 at 16:18

Richard L

1,2217 silver badges10 bronze badges

Thanks Richard That works very well. I realize the schema is totally flawed, but I'm taking a csv and creating a sqlite database in memory, then using that to make queries against. So I am somewhat limited in having to do static text entries.
– valentine
Commented Jun 26, 2011 at 16:35

Add a comment |

pratik garg · Accepted Answer · 2011-06-27 08:56:44Z

1

in oracle database you can write like below query to show required solution :-

select * from table_name
pivot (count(*) for action in ('run','jump','drive'))

this will give the desired output..

edited Jun 27, 2011 at 8:56

answered Jun 27, 2011 at 6:27

pratik garg

3,3421 gold badge19 silver badges21 bronze badges

Add a comment |

Collectives™ on Stack Overflow

SQL Group by one column, count entries in another

4 Answers 4

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Related