SQL group records by time

Question

SQL group records by time

Navigation

#1 by (0 votes)

-1

I have a table in MySQL that stores events by time with the following structure:

id / num_veh / time

I would like to create a SQL query that returns the events in groups by time range. For example:

From 01:00 to 06:00 - 500 items / From 07:00 to 12:00 - 800 items

I researched a lot about it and I believe I'm pretty close with the following SQL:

    SELECT grupos, SUM(num_veh) as quantidade
FROM (
     SELECT( CASE
         WHEN DATE_FORMAT(time, '%H:%i:%s') > '00:00' 
         && DATE_FORMAT(time, '%H:%i:%s') < '06:00' THEN '00h às 06h'
         WHEN DATE_FORMAT(time, '%H:%i:%s') > '06:00' 
         && DATE_FORMAT(time, '%H:%i:%s') < '12:00' THEN '06h às 12h'
         ELSE 0 END)
     as grupos
     FROM tb_events
     ) as acessos
GROUP BY grupos
ORDER BY quantidade;

However, it is not working and returns the error:

Unknown 'num_veh' column in 'field list'

Does anyone know how to fix this problem? Or can you tell me how I could do differently to find a solution?

sql sintaxe

asked by anonymous 16.11.2018 / 03:56

1 answer

Zero right - Input What is the best database to store a list of information [closed]

score 0 · Answer 1

You have failed to enter the "num_veh" column in your sub-select

   SELECT grupos, SUM(num_veh) as quantidade
    FROM (
         SELECT num_veh, 
             ( CASE
             WHEN DATE_FORMAT(time, '%H:%i:%s') > '00:00' 
             && DATE_FORMAT(time, '%H:%i:%s') < '06:00' THEN '00h às 06h'
             WHEN DATE_FORMAT(time, '%H:%i:%s') > '06:00' 
             && DATE_FORMAT(time, '%H:%i:%s') < '12:00' THEN '06h às 12h'
             ELSE 0 END)
         as grupos
         FROM tb_events
         ) as acessos
    GROUP BY grupos
    ORDER BY quantidade;