0

I have this query and the the result is in multiple rows, each one is a event and his respective count number.

SELECT u.name, l.event, COUNT (l.event)
FROM log AS l LEFT JOIN user AS u ON u.id = l.userid
GROUP BY u.name, l.event

The result is like this:

------------------------------------------
|    user    |    event    |    count    |
------------------------------------------
| user_1     | event_1     | 12          |
| user_1     | event_2     | 6           |
| user_1     | event_3     | 9           |
| user_2     | event_1     | 16          |
| ...        | ...         | ...         |

The problem is that i need these count as parameters (columns) in a single result row, where each row represents a single user, something like this:

--------------------------------------------------------------
|    user    |    event_1    |    event_2    |    event_3    |
--------------------------------------------------------------
| user_1     | 12            | 6             | 9             |
| user_2     | 16            | 0             | 13            |
| ...        | ...           | ...           | ...           |

Maybe i can do this with a select query? With some kind of function that does a loop or something similar?

Thank you!

EDIT 1: I do not know the events to write them in the code

EDIT 2: I see that there is no trivial way to do this with a simple sql query, so i use python with pandas to do this. Take a look in How to convert a column of string to numerical?

Sandro Motyl
  • 109
  • 6
  • You can use PIVOT. I have had a similar type of question. PIVOT on the l.event field. Need to brush up on my syntax before I right the code for you however. – Mike Mirabelli Sep 05 '17 at 00:57
  • Search for crosstab. Who is the consumer of this query? Outputing json is the easy way to have it completely dynamic. Is it ok to output json? – Clodoaldo Neto Sep 05 '17 at 14:29

2 Answers2

0

If you know the events, you can use conditional aggregation:

SELECT name,
       MAX(CASE WHEN event = 'event_1' THEN cnt ELSE 0 END) as event_1,
       MAX(CASE WHEN event = 'event_2' THEN cnt ELSE 0 END) as event_2,
       MAX(CASE WHEN event = 'event_3' THEN cnt ELSE 0 END) as event_3
FROM (SELECT u.name, l.event, COUNT(l.event) as cnt
      FROM user u LEFT JOIN 
           log l
           ON u.id = l.userid
      GROUP BY u.name, l.event
     ) ul
GROUP BY name;

I switched the LEFT JOIN. It seems more likely that you want to keep all the users, even if there are no matching log messages.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • unfortunately i do not know the events, in fact there are about 80 events in the database. anyway, the tip for the left join is very useful, thanks. – Sandro Motyl Sep 05 '17 at 01:00
0

Try this and see if it works..

Select [event_1],[event_2],[event_3] from 

(SELECT u.name, l.event FROM log AS l LEFT JOIN user AS u ON u.id = l.userid ) s1

PIVOT (COUNT(l.event) FOR u.name in ([event_1],[event_2],[event_3])) as p1
Mike Mirabelli
  • 402
  • 3
  • 16