SQL Group by Count String occurance-CodePudding

I am wanting to count the occurrences of a specific string in SQL. I have UserID's which are unique and each user can carry out an "action". I've tried a few things but still cannot get it to work.

So these actions can be "throw" "pickup" "craft"

SELECT userid, COUNT(action)
FROM `playeractions`
GROUP BY action;

userid	COUNT(action)
7656119	129
76561194	4

Expected results required

userid	throw	pickup	craft
7656119	29	100	0
76561194	2	2	0

Existing data of the table

userid	action
7656119	throw
76561194	pickup
76561194	pickup
76561194	throw

CodePudding user response：

You need conditional aggregation:

SELECT userid, 
       COUNT(CASE WHEN action = 'throw' THEN 1 END) AS throw,
       COUNT(CASE WHEN action = 'pickup' THEN 1 END) AS pickup,
       COUNT(CASE WHEN action = 'craft' THEN 1 END) AS craft
FROM playeractions 
GROUP BY userid;

Depending on the database that you use the code may be simplified.

CodePudding user response：

you can simple privot the data

SELECT
    userid,
    SUM(action = 'throw') as 'throw',
    SUM(action = 'pickup') as 'pickup',
    SUM(action = 'craft') as 'craft'
FROM table1
GROUP BY userid