Group By By Data

Asked

Viewed 857 times

2

I have a table called tsc, with the properties ID and DataHoraOcorrencia. I need to check the amount of ID for DataHoraOcorrecia.

SQL

SELECT 
    DataHoraOcorrencia as DataHora, 
    COUNT(tsc.ID) as Quantidade 
    FROM tsc 
    WHERE DataHoraOcorrencia BETWEEN '2017-07-01' AND '2017-07-30';

My problem is in the shape of DataHoraOcorrecia, because if I make a between date in the format aaaa-mm-dd, it brings me with the hours, and need only by day. In case it groups by date and time and not just by date.

The entity DataHoraOcorrencia is in format aaaa-mm-dd hh:ii:ss.

mysql sql database datetime date

2 answers

4


Doing SQL this way already solves (just adding the function date() before the date field: 

SELECT 
    DataHoraOcorrencia as DataHora, 
    COUNT(tsc.ID) as Quantidade 
    FROM tsc 
    WHERE Date(DataHoraOcorrencia) BETWEEN '2017-07-01' AND '2017-07-30'
 GROUP BY DataHoraOcorrencia;

  • In the case I still had to use GROUP BY. It worked, vlw.

2

Here is an example, used in SQL Sever

Declare @tabela as table(DataHoraOcorrencia datetime, Id int)
insert into @tabela (DataHoraOcorrencia, Id)
Values     
('10-07-2017 12:48:37',1),
('09-07-2017 10:40:30',2),
('10-07-2017 12:40:30',3),
('09-07-2017 12:30:30',4),
('10-07-2017 11:00:30',5)


SELECT 
    convert(Date,a.DataHoraOcorrencia) as DataHora, 
    COUNT(a.ID) as Quantidade 
    From @tabela a
    Where convert(Date,DataHoraOcorrencia) BETWEEN '2017-07-01' AND '2017-07-30'
   Group by convert(Date,a.DataHoraOcorrencia)

Browser other questions tagged mysql sql database datetime date

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