SQL for Pivot and AVG using MySQL database version 5.5.62

user_15117016 0
Viewed 29

Using this query on MySQL database version 5.5.62

SELECT
    COUNT( CASE WHEN MONTH ( tData ) = 1 THEN 'Jan' END ) Jan,
    COUNT( CASE WHEN MONTH ( tData ) = 2 THEN 'Feb' END ) Feb,
    COUNT( CASE WHEN MONTH ( tData ) = 3 THEN 'Mar' END ) Mar,
    tname,
    AVG( DATEDIFF( COALESCE ( tClosed, tOpen ), tData )), 1 ) 
    END AS `avg` 
FROM
    `tbl_2021` 
WHERE
        tname IN ( 'ST00' ) 
GROUP BY
    MONTH ( tData );

the return is

+-------+-------+-------+-------+-----+
| Jan   | Feb   | Mar   | tname | avg |
+-------+-------+-------+-------+-----+
| 17970 |     0 |     0 | ST00  | 8.4 |
|     0 | 14429 |     0 | ST00  | 5.2 |
|     0 |     0 | 22685 | ST00  | 0.4 |
+-------+-------+-------+-------+-----+

but I would like to have instead something like:

+-------+-------+-------+-------+---------+---------+---------+
| Jan   | Feb   | Mar   | tname | avg_Jan | avg_Feb | avg_Mar |
+-------+-------+-------+-------+---------+---------+---------+
| 17970 | 14429 | 22685 | ST00  |   8.4   |   5.2   |   0.4   |
+-------+-------+-------+-------+---------+---------+---------+

Any help would greatly appreciate... Thank you.

pivot mysql
1 Answers

You can use the CASE statement in the AVG function in the same way as the COUNT function. Also, it isn't necessary to use a GROUP BY clause.

SELECT
    COUNT( CASE WHEN MONTH ( tData ) = 1 THEN 'Jan' END ) Jan,
    COUNT( CASE WHEN MONTH ( tData ) = 2 THEN 'Feb' END ) Feb,
    COUNT( CASE WHEN MONTH ( tData ) = 3 THEN 'Mar' END ) Mar,
    MAX(tname),
    AVG( CASE WHEN MONTH ( tData ) = 1 THEN DATEDIFF( COALESCE ( tClosed, tOpen ), tData ) END ) avg_Jan,
    AVG( CASE WHEN MONTH ( tData ) = 2 THEN DATEDIFF( COALESCE ( tClosed, tOpen ), tData ) END ) avg_Feb,
    AVG( CASE WHEN MONTH ( tData ) = 3 THEN DATEDIFF( COALESCE ( tClosed, tOpen ), tData ) END ) avg_Mar
FROM
    `tbl_2021` 
WHERE
        tname IN ( 'ST00' )
user_138410160
Related