Geek Answers Handbook

SQL Group one column, accounts in another column

I am using sqlite3 database with the following table.

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

I want to get this conclusion

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

The closest I came with this, but I would like to have one line as shown 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 |

In addition, I will also need to have WHERE statements in the request.

Did I really think it would work at night?

+4
source share
4 answers

What you are trying to do is called a crosstab . This is usually available as a function called a pivot table in Excel and other spreadsheet programs.

I found a blog article that will help you with this use of SQL. Check pivot-table-hack-in-sqlite3-and-mysql

+1
source

You can also do what you want using aggregated amounts and CASE conditions, such as:

 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 still have to change the request every time additional actions are added.

+2
source

I do not know SQLLite very well, but it seems to me that you can use subqueries or temporary tables. Using mssql you can 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 will need to be rewritten every time you create a different type of action. You should probably add an index to get speed on the table. But first check the query plan with the "real" data.

+1
source

in the oracle database you can write, as below, a query to show the required solution: -

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

this will give the desired result.

0
source

More articles:


All Articles