ARJUN ARJUN - 9 months ago 64
SQL Question


select day(dd,DATETIME),count(*) from (select distinct * from logs_currentmonth)a
where DATETIME>='01-OCT-2016'
group by day(dd,DATETIME)

i was using this query to find out data count but there is no data with date 28/10/2016 but my query result showing data count with that date why this is happening i am not understating

give me some trick while handling date

Answer Source

Because there is data for some other month that has a 28th. I would recommend:

select cast([datetime] as date), count(*)
from (select distinct cm.*
      from logs_currentmonth cm
     ) cm
where [datetime] >= '2016-10-01'
group by cast([datetime] as date)
order by cast([datetime] as date);

If you don't need the select distinct, then don't use it! It just slows down the query.