Search code examples
mysqlsqlpivotcrosstab

MYSQL query to sum upp all values from a certain fields

I have a table with columns:

db_name and verb

The table is holding the usage data of all requests may by the certain database.

The query I'm using now is:

SELECT db_name, verb, COUNT(*) FROM request GROUP BY db_name, verb order by db_name

Which returns:

enter image description here

But what I would like to have, is for the query to return all distinct db_name with summed up all distinct verbs.

Something like:

db_name      GET    POST   DELETE   UPDATE
username0     99      89      100     8299
username1     19      33      120     3111

How should I do this, or where should I look?

Solution

  • MySQL has a convenient syntax for conditional counts:

    SELECT db_name, 
       SUM(verb = 'GET') as cnt_get,
       SUM(verb = 'POST') as cnt_post,
       SUM(verb = 'DELETE') as `cnt_delete,
       SUM(verb = 'UPDATE') as `cnt_update
FROM request
GROUP BY db_name
ORDER BY db_name;

    Specifically, MySQL treats boolean values as numbers in an arithmetic context, with 1 for true and 0 for false. That makes it easy to sum up the values.