CODEWITHSUNDEEP
sql
Nathan
Nathan

Reputation: 17

SQL Group by Count String occurance

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

Upvotes: 1

Views: 648

Answers (2)

forpas
forpas

Reputation: 164089

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.

Upvotes: 2

nbk
nbk

Reputation: 49375

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

Upvotes: 1

Related Questions