Dfyjkt

I have a kind of tricky question for this query. First the code:

SELECT user_type.user_type_description,COUNT(incident.user_id) as Quantity
FROM incident
INNER JOIN user ON incident.user_id=user.user_id
INNER JOIN user_type ON user.user_type=user_type.user_type
WHERE incident.code=2
GROUP BY user.user_type

What Am I doing?

For example, I am counting police reports of robbery, made from different kind of users. In my example, "admin" users reported 6 incidents of code "2" (robbery) and so on, as is showed in 'where' clause (incident must be robbery, also code 2).

this brings the following result:

+-----------------------+----------+
| user_type_description | Quantity |
+-----------------------+----------+
| Admin | 6 |
| Moderator | 8 |
| Fully_registered_user | 8 |
| anonymous_user | 9 |
+-----------------------+----------+

Basically Admin,Moderator and Fully_registered_user are appropriately registered users. I need to add them in a result where it shows like:

+--------------+------------+
| Proper_users | Anonymous |
+--------------+------------+
| 22 | 9 |
+--------------+------------+

I am not good with sql. Any help is appreciated. Thanks.

You can try to use condition aggregate function base on your current result set.

SUM with CASE WHEN expression.

SELECT SUM(CASE WHEN user_type_description IN ('Admin','Moderator','Fully_registered_user') THEN Quantity END) Proper_users,
 SUM(CASE WHEN user_type_description = 'anonymous_user' THEN Quantity END) Anonymous
FROM (
 SELECT user_type.user_type_description,COUNT(incident.user_id) as Quantity
 FROM incident
 INNER JOIN user ON incident.user_id=user.user_id
 INNER JOIN user_type ON user.user_type=user_type.user_type
 WHERE incident.code=2
 GROUP BY user.user_type
) t1

You just need conditional aggregation:

SELECT SUM( ut.user_type_description IN ('Admin', 'Moderator', 'Fully_registered_user') ) as Proper_users,
 SUM( ut.user_type_description IN ('anonymous_user') as anonymous
FROM incident i INNER JOIN
 user u
 ON i.user_id = u.user_id INNER JOIN
 user_type ut
 ON u.user_type = ut.user_type
WHERE i.code = 2;

Notes:

• Table aliases make the query easier to write and to read.


• This uses a MySQL shortcut for adding values -- just just adding the booelean expressions.

I would solve it with a CTE, but it would be better to have this association in a table.

WITH
user_type_categories
AS
(
 SELECT 'Admin' AS [user_type_description] , 'Proper_users' AS [user_type_category] 
 UNION SELECT 'Moderator' AS [user_type_description] , 'Proper_users' AS [user_type_category] 
 UNION SELECT 'Fully_registered_user' AS [user_type_description] , 'Proper_users' AS [user_type_category] 
 UNION SELECT 'anonymous_user' AS [user_type_description] , 'Anonymous' AS [user_type_category] 
)
SELECT 
 CASE WHEN utc.[user_type_category] = 'Proper_users' THEN
 SUM(incident.user_id) 
 END AS [Proper_Users_Quantity]
 , CASE WHEN utc.[user_type_category] = 'Anonymous' THEN
 SUM(incident.user_id) 
 END AS [Anonymous_Quantity]
FROM 
 [incident]
 INNER JOIN [user] ON [incident].[user_id] = [user].[user_id]
 INNER JOIN [user_type] ON [user].[user_type] = [user_type].[user_type]
 LEFT JOIN user_type_categories AS utc ON utc.[user_type_description] = [user_type].[user_type_description]
WHERE 
 [incident].[code] = 2