CODEWITHSUNDEEP
mysqlsql
okonomiyaki
okonomiyaki

Reputation: 3

Need assistance in creating query to sum up number of occurrences in a table

I have a table that has data like this:

event | session id | uid 
---------------------
foo   | 1          | a
bar   | 1          | a
foo   | 2          | b
cat   | 2          | b
cat   | 3          | a
cat   | 4          | c
foo   | 5          | c
cat   | 5          | c
foo   | 6          | b
foo   | 7          | a
dog   | 8          | c
dog   | 9          | b
bat   | 10         | c

I would like to create a query to return the results in the following format:

uid | event | # event used | # of sessions for uid
a   | foo   | 2            | 4 
a   | bar   | 1            | 4
a   | cat   | 1            | 4
b   | foo   | 2            | 4
b   | cat   | 1            | 4
b   | dog   | 1            | 4
c   | cat   | 2            | 5
c   | foo   | 1            | 5
c   | dog   | 1            | 5
c   | bat   | 1            | 5

I tried using:

select uid, event, count(*) from events where uid in (select uid from events group by uid) group by uid;

but that didn't work as expected.

Any help would be greatly appreciated.

Upvotes: 0

Views: 39

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416081

select t1.uid, t1.event, count(*) as `# event used`, t2.total_events as `# of sessions for uid`
form table t1
inner join (
   select uid, count(*) as total_events
   from table
   group by uid
) t2 on t2.uid = a.uid
group by t1.uid, t1.event, t2.total_events
order by t1.uid, `# event used` desc

Upvotes: 1

digitai
digitai

Reputation: 1842

This can be achieved with CASE statements:

select uid,  count(case when event=`foo` then uid else 0 end)  as `foo`,   
count(case when    event=`bar` then uid else 0 end)  as `bar`,  
count(case when event=`cat` then uid else 0 end)  as `cat`, 
count((case when event=`foo` then uid else 0 end) +  count(case when    event=`bar` then uid else 0 end) + count(case when event=`cat` then uid else 0 end))  as totaL_events` 
from  events group by 1;

This will give you a table like this:

uid | foo | bar | cat | total_events
a     2     1     1       4
b     2     1     1       4

For a different table, use this:

select uid, event, count(uid) as event_used  from events group by 1, 2

Here the table is a less fancy one:

uid | event | total_events
a     foo     2     
a     bar     1   
b     foo     2

Keep in mind that it`s not possible or a t least not efficient to summarize the total quantity of events for each user in each different case, as you requested, it can be achieved just by uid and type of event.

Upvotes: 0

Related Questions