What's the best way to split my results by day?

Question

not sure where to go with this one. I know I need to split the date and time up from 'createdon' but then I'm stumped.

I can bring back values with the query but I have to manually enter each day.

`SELECT 
sum(CASE WHEN title LIKE '%Environmental%' THEN 1 ELSE 0 END)As Environmental
,sum(CASE WHEN title LIKE '%Let%' THEN 1 ELSE 0 END)As Let
,sum(CASE WHEN title LIKE '%Lease%' THEN 1 ELSE 0 END)As Lease
,sum(CASE WHEN title LIKE '%Pay%' THEN 1 ELSE 0 END)As Paym
,sum(CASE WHEN title LIKE '%Manage%' THEN 1 ELSE 0 END)As Manage
,sum(CASE WHEN title LIKE '%Rent%' THEN 1 ELSE 0 END)As Rent
,sum(CASE WHEN title LIKE '%Works%' THEN 1 ELSE 0 END)As Works

FROM incident WHERE (createdon > ('01/09/2021 00:01') AND createdon < ('01/09/2021 23:59'))`

Ideally, this is what I'm trying to bring back

Something like `convert(date, createdon) = '20210109'` (syntax may depend on your dbms (which is why you should tag it)) — HoneyBadger, Feb 08 '21 at 10:52
[Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Feb 08 '21 at 11:47

score 0 · Answer 1 · answered Feb 08 '21 at 11:45

SELECT 
  sum(CASE WHEN title LIKE '%Environmental%' THEN 1 ELSE 0 END)As Environmental
  ,sum(CASE WHEN title LIKE '%Let%' THEN 1 ELSE 0 END)As Let
  ,sum(CASE WHEN title LIKE '%Lease%' THEN 1 ELSE 0 END)As Lease
  ,sum(CASE WHEN title LIKE '%Pay%' THEN 1 ELSE 0 END)As Paym
 ,sum(CASE WHEN title LIKE '%Manage%' THEN 1 ELSE 0 END)As Manage
 ,sum(CASE WHEN title LIKE '%Rent%' THEN 1 ELSE 0 END)As Rent
,sum(CASE WHEN title LIKE '%Works%' THEN 1 ELSE 0 END)As Works
,createdon 
 FROM incident WHERE createdon between <your_start_date> and <your_end_date>
   GROUP BY createdon

score 0 · Answer 2 · answered Feb 08 '21 at 11:54

You need to aggregate by the date. One method looks like this:

SELECT CAST(createdon as DATE),
       SUM(CASE WHEN title LIKE '%Environmental%' THEN 1 ELSE 0 END)As Environmental,
       SUM(CASE WHEN title LIKE '%Let%' THEN 1 ELSE 0 END) As Let,
       SUM(CASE WHEN title LIKE '%Lease%' THEN 1 ELSE 0 END) As Lease,
       SUM(CASE WHEN title LIKE '%Pay%' THEN 1 ELSE 0 END) As Paym,
       SUM(CASE WHEN title LIKE '%Manage%' THEN 1 ELSE 0 END) As Manage,
       SUM(CASE WHEN title LIKE '%Rent%' THEN 1 ELSE 0 END) As Rent,
       SUM(CASE WHEN title LIKE '%Works%' THEN 1 ELSE 0 END)A s Works,
FROM incident 
GROUP BY CAST(createdon as DATE)
ORDER BY CAST(createdon as DATE);

The problem is that date/time functions are notoriously database dependent. Here are some syntaxes:

CAST(createdon as DATE) should work in SQL Server, MySQL, and Postgres.
TRUNC(createdon) works in Oracle.
DATE() in SQLite.

What's the best way to split my results by day?

2 Answers2