CODEWITHSUNDEEP
sqlgoogle-bigquery
Jameel Jamal
Jameel Jamal

Reputation: 63

How to create a summary table

Given a table that lists actions of actors (I hide irrelevant timestamp column), we would like to have a summary table listing a row per actor with counts per action he did. we have predefined three possible actions

Standard SQL or Google BigQuery Syntax


Actor   |   Action
_________________
Sam         shoot
Sam         shoot
Sam         heal
Sam         dead
Bob         shoot
Bob         shoot
Bob         shoot
Bob         dead

To


Actor   |  shoot  |  heal   | Dead
_____________________________________
Sam        2          1         1
Bob        3          0         1

Upvotes: 0

Views: 328

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

If you know the columns that you want, use countif():

select actor,
       countif(action = 'shoot') as shoot,
       countif(action = 'heal') as heal,
       countif(action = 'dead') as dead
from t
group by actor;

If you don't, then you have a challenge, because SQL queries tend to need to know what columns are in the result set. One workaround is to put the values in rows rather than columns:

actor    action     count
 sam     shoot        2
 . . .

That is:

select actor, action, count(*)
from t
group by actor, action;

(This does not include 0 counts, but the query can be adjusted fro this.)

Or to use JSON or arrays to store the values for every action.

Upvotes: 1

Related Questions