CODEWITHSUNDEEP
mysql
Mario
Mario

Reputation: 4998

How to sum dinamilcy calculated values in each row?

I need your advice in this scenario.

Having the following SQL query

SELECT 
    MONTHNAME(r.date_of_application) month_name,
    MONTH(r.date_of_application) month,
    SUM(CASE
        WHEN status = 'pending' THEN 1
        ELSE 0
    END) pending,
    SUM(CASE
        WHEN status = 'attended' THEN 1
        ELSE 0
    END) attended,
    SUM(CASE
        WHEN status = 'absent' THEN 1
        ELSE 0
    END) absent,
    SUM(CASE
        WHEN status = 'canceled' THEN 1
        ELSE 0
    END) canceled
FROM
    request r
        INNER JOIN
    user u ON u.id = r.user_id
GROUP BY month_name , month
ORDER BY 2 DESC;

I get this result

+------------+-------+---------+----------+--------+----------+
| month_name | month | pending | attended | absent | canceled |
+------------+-------+---------+----------+--------+----------+
| October    |    10 |       4 |        1 |      0 |        1 |
+------------+-------+---------+----------+--------+----------+

Now I want to sum the values of the columns pending, attended, absent and canceled an add the result in a new column total.

This is the expected result.

+------------+-------+---------+----------+--------+----------+-------+
| month_name | month | pending | attended | absent | canceled | total |
+------------+-------+---------+----------+--------+----------+-------+
| October    |    10 |       4 |        1 |      0 |        1 |    6 |
+------------+-------+---------+----------+--------+----------+-------+

This query could have many results

Thanks in advance

Upvotes: 0

Views: 45

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Just add a COUNT(*) aggregation term to capture the entire count of all statuses:

SELECT 
    MONTHNAME(r.date_of_application) month_name,
    MONTH(r.date_of_application) month,
    COUNT(CASE WHEN status = 'pending'  THEN 1 END) pending,
    COUNT(CASE WHEN status = 'attended' THEN 1 END) attended,
    COUNT(CASE WHEN status = 'absent'   THEN 1 END) absent,
    COUNT(CASE WHEN status = 'canceled' THEN 1 END) canceled,
    COUNT(*) total
FROM request r
INNER JOIN user u
    ON u.id = r.user_id
GROUP BY
    month_name , month
ORDER BY 2 DESC;

Note: If you want the total to include only the four status values which you are currently tallying, then use a conditional aggregation:

COUNT(CASE WHEN status IN ('pending', 'attended', 'absent', 'cancelled')
           THEN 1 END) AS total

Upvotes: 1

Related Questions