When using MYSQL GROUP BY WITH ROLLUP on Year(date) and Month(date) i am not able to change Null to 'Total'

Question

I want to create a table using GROUP BY WITH ROLLUP and get total rows instead of null.

$sql ="SELECT 
            IF(YEAR(transaktioner.datum) is null or YEAR(transaktioner.datum) = '','Total',YEAR(transaktioner.datum)) as Year,
            IF(MONTH(transaktioner.datum) is null or MONTH(transaktioner.datum) = '','Total',MONTH(transaktioner.datum)) as Month,                      
            SUM(transaktioner.belopp)*0.01 as Belopp
        FROM transaktioner 
            GROUP BY 
                Year,
                Month
            WITH ROLLUP
   ";


$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "||Year:". $row["Year"]."|MONTH:" . $row["Month"]. "|Sum:" . $row["Belopp"]. " ";
    }
} else {
    echo "0 results";
}
$conn->close();

However the output does not give me True or false instead it gives me:

|Year:2016|MONTH:1|Sum:408.1797 |
|Year:2016|MONTH:10|Sum:-70.6915 |
|Year:2016|MONTH:11|Sum:-189.0771 |
|Year:2016|MONTH:12|Sum:-51.7362 |
|Year:2016|MONTH:2|Sum:67.5001 |
|Year:2016|MONTH:3|Sum:132.6177 |
|Year:2016|MONTH:4|Sum:-36.4121 |
|Year:2016|MONTH:5|Sum:-121.8800 |
|Year:2016|MONTH:6|Sum:294.5811 |
|Year:2016|MONTH:7|Sum:76.3505 |
|Year:2016|MONTH:8|Sum:-201.3231 |
|Year:2016|MONTH:9|Sum:-62.8723 |
|Year:2016|MONTH: |Sum:245.2368 |
|Year:2017|MONTH:1|Sum:156.2617 |
|Year:2017|MONTH:2|Sum:-166.7156 |
|Year:2017|MONTH:3|Sum:-183.3601 |
|Year:2017|MONTH:4|Sum:-213.6732 |
|Year:2017|MONTH:5|Sum:-149.0897 |
|Year:2017|MONTH:6|Sum:-120.2097 |
|Year:2017|MONTH:7|Sum:-302.1064 |
|Year:2017|MONTH:8|Sum:-183.0638 |
|Year:2017|MONTH:9|Sum:-119.3371 |
|Year:2017|MONTH: |Sum:-1281.2939 |
|Year:    |MONTH: |Sum:347.6165 

It is blank instead of saying 'Total'. What am i doing wrong? I have tried with ifnull() aswell but same problem.

Answer 1

If you want to do the check in SQL, you have to put the grouped query into a subquery, and process the output in the main query:

SELECT IFNULL(Year, 'Total') AS Year,
       IFNULL(Month, 'Total') AS Month,
       Belopp
FROM (
    SELECT YEAR(transaktioner.datum) AS Year,
           MONTH(transaktioner.datum) AS Month,
           SUM(transaktioner.belopp) * 0.01 AS Belopp
    FROM transaktioner
    GROUP BY Year, Month
    WITH ROLLUP
) AS x

You could also just do the grouped query, and check for NULL in PHP:

while($row = $result->fetch_assoc()) {
    if ($row["Year"] == null) {
        $row["Year"] = "Total";
    }
    if ($row["Month"] == null) {
        $row["Month"] = "Total";
    }
    echo "||Year:". $row["Year"]."|MONTH:" . $row["Month"]. "|Sum:" . $row["Belopp"]. " ";
}