CODEWITHSUNDEEP
sqlsqlitecountgroup-by
valentine
valentine

Reputation: 63

SQL Group by one column, count entries in another

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?

Upvotes: 6

Views: 7776

Answers (4)

pratik garg
pratik garg

Reputation: 3342

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..

Upvotes: 1

Waleed Al-Balooshi
Waleed Al-Balooshi

Reputation: 6406

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.

Upvotes: 3

Mridul Kashatria
Mridul Kashatria

Reputation: 4187

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

Upvotes: 1

Richard L
Richard L

Reputation: 1221

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.

Upvotes: 1

Related Questions