SQL group actions-CodePudding

How to calculate how many actionType each userId has and display each actionType in a separate column

Table:

CREATE TABLE history (
    userId VARCHAR(40),
    actionType ENUM('ban', 'unban', 'mute', 'unmute')
);

Example content in table: enter image description here

Output example: enter image description here

CodePudding user response：

This is a Pivoting question. and below is the query that can be useful to solve the problem that I am thinking of and there might be other optimised solution as well.

SELECT  P.userid,
COUNT(CASE WHEN P.actiontype='ban' THEN 1 ELSE NULL END) AS 'ban',
COUNT(CASE WHEN P.actiontype='unban' THEN 1 ELSE NULL END) AS 'unban',
COUNT(CASE WHEN P.actiontype='mute'THEN 1 ELSE NULL END) AS 'mute',
COUNT(CASE WHEN P.actiontype='unmute' THEN 1 ELSE NULL END) AS 'unmute'
FROM    history P
GROUP BY P.userid;

Tested in the online tool db<>fuddle

CodePudding user response：

Your sample data does not match your table definition. According to your table definition, the action type "kick" is not valid. But both your input data and the expected outcome contain rows with this type. Therefore, this answer assumes your table definition is incorrect. You can just build the sum for the different action types. According to your sample input and desired result, these types are "mute", "kick" or "ban". So you can use a query like this:

SELECT userid,
SUM(actiontype='mute') AS mute,
SUM(actiontype='kick') AS kick,
SUM(actiontype='ban') AS ban
FROM history
GROUP BY userid;

If you also want to show the total of those amounts per day, you can do this:

SELECT userid,
SUM(actiontype IN ('mute', 'kick', 'ban')) AS total,
SUM(actiontype='mute') AS mute,
SUM(actiontype='kick') AS kick,
SUM(actiontype='ban') AS ban
FROM history
GROUP BY userid;

If you need further assistance, please review your question and make sure the sample data and your table definition are correct.