How to use GROUP BY with subquery range?-CodePudding

I'm quite new here and I am tinkering with MYSQL to get a sort of pivot table.

For now I'm blocked here :

SELECT `range`,
   Sum(IF(`Vrange` = '< 5',1,0)) as `<5`,
   Sum(IF(`Vrange` = ' 5-10',1,0)) as `5-10`,
   Sum(IF(`Vrange` = ' 10-15',1,0)) as `10-15`,
   Sum(IF(`Vrange` = ' 15-20',1,0)) as `15-20`,
   Sum(IF(`Vrange` = ' 20-25',1,0)) as `20-25`,
   Sum(IF(`Vrange` = ' 20-25',1,0)) as `20-25`,
   Sum(IF(`Vrange` = '> 30',1,0)) as `>30`
FROM(
     select `Time`,`HDG`, `Vitesse`,
        case 
            when `HDG` between 1 and 90 then ' 0-90' 
            when `HDG` between 91 and 180 then ' 91-180' 
            when `HDG` between 181 and 270 then ' 181-270' 
        else '271-360' 
        end as `range`,
        case 
            when `Vitesse` between 0 and 5 then '< 5' 
            when `Vitesse` between 6 and 10 then ' 5-10' 
            when `Vitesse` between 11 and 15 then ' 10-15' 
            when `Vitesse` between 16 and 20 then ' 15-20' 
            when `Vitesse` between 21 and 25 then ' 20-25' 
            when `Vitesse` between 25 and 30 then ' 25-30' 
        else '> 30' 
        end as `Vrange`
     from DataPort 
     WHERE `Time` > now() - interval 1 day  
     ORDER BY `Time` DESC

)as SQ
GROUP BY `range`;

I get the following anwser :

|  range  |    <5   |    5-10    |   ...
|---------|---------|------------|--------
|   0-90  |     5   |     3      |
| 180-270 |     12  |     20     |

And I would like to display all items of range i.e. 0-90 / 91-180 / 181-270 / 271-360 in each row. How is it possible ? As follow :

|  range  |    <5   |    5-10    |   ...
|---------|---------|------------|--------
|   0-90  |     1   |     1      |
|  91-180 |0 or null| 0 or null  |
| 180-270 |     12  |     20     |
| 271-360 |0 or null| 0 or null  |

Many thanks in advance

CodePudding user response：

I am not sure if anything is wrong with your code... Are you saying you are missing "91-180" and "271"360"? Are you sure you have rows that match that range in your subquery?

CodePudding user response：

Welcome to S/O. This should help get what you had going. You did not need to do an explicit pre-query to get ranges, then sum again in the outer query for the counts.

select
        case when DP.HDG >= 0 and DP.HDG <= 90 then '0-90'
            when DP.HDG > 90 and DP.HDG <= 180 then '91-180'
            when DP.HDG >= 180 and DP.HDG <= 270 then '181-270'
            else '271-360' end Range,
            sum( case when DP.Vitesse >= 0 and DP.Vitesse < 5 then 1 else 0 end ) ' < 5',
            sum( case when DP.Vitesse >= 5 and DP.Vitesse < 10 then 1 else 0 end ) '5-10',
            sum( case when DP.Vitesse >= 10 and DP.Vitesse < 15 then 1 else 0 end ) '10-15',
            sum( case when DP.Vitesse >= 15 and DP.Vitesse < 20 then 1 else 0 end ) '15-20',
            sum( case when DP.Vitesse >= 20 and DP.Vitesse < 25 then 1 else 0 end ) '20-25',
            sum( case when DP.Vitesse >= 25 and DP.Vitesse < 30 then 1 else 0 end ) '25-30',
            sum( case when DP.Vitesse >= 30 then 1 else 0 end ) '>30'
    from
        DataPort DP
    where
       WHERE DP.`Time` > now() - interval 1 day 
    group by
        case when DP.HDG >= 0 and DP.HDG <= 90 then '0-90'
            when DP.HDG > 90 and DP.HDG <= 180 then '91-180'
            when DP.HDG >= 180 and DP.HDG <= 270 then '181-270'
            else '271-360' end

Now, having said that, and the above will work, I would like to point out some less-than-optimal parts of it.

Your "HDG", I believe is a directional Heading and will always be technically 0-359 degrees as 360 is actually back to 0.

In your Vitesse range brackets, and not knowing if any fractional / decimal values or not, but you are using the labels twice, such as 5-10 and 10-15. Shouldn't 10 only be within one of the brackets? Your between was testing between 11 and 15, so shouldn't the header group also match?

Your result columns should be named columns. Not spaces, and especially not special characters, dashes, etc. The results should be a table with direct column names. It is the part of your OUTPUT such as report or web that has heading columns with proper context rather than naming the columns as you were attempting.

Finally, careful on your column names, such as 'Time' Try NOT to use reserved keywords within your SQL table column definitions. Take a look at the commands available, function names, etc. Instead of just time, maybe a EntryTime, LogTime, CreateTime, or similar. A bit more explicit context and you'll avoid having to add tick marks to everything. Also, by qualifying with table.column or alias.column helps prevent ambiguity when joining to multiple tables having similar column names.

Just trying to suggest improvements for this and future as you grow with SQL.