SQL group records by time

Asked

Viewed 71 times

-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 an SQL query that returns 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’ve done a lot of research on this and I believe I’m very 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 they tell me how I could do differently to find a solution?

sql syntax
  • 1

    You are selecting columns that do not exist according to the code you posted. In fact, I don’t even see where the column num_veh is.

  • Isn’t there anything you haven’t put in your query? Is that field num_veh does not exist in consultation.

  • Probably the error does not refer to this query.

  • Sorry, I tried to simplify the context of my application by replacing num_veh with Qtd.

1 answer

0

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;

Browser other questions tagged sql syntax

You are not signed in. Login or sign up in order to post.