CODEWITHSUNDEEP
javascriptoracle-database
jansetk
jansetk

Reputation: 29

Writing query for different months

I am trying to make a graph that shows company's total sale per day. That is my query to find today's total sale:

select count(*) from registered_devices where  status = 1;

and this is the query to find total sale in 7 days ago:

 select count(*) from registered_devices where sysdate - 7 <TO_DATE('19700101','yyyymmdd') + ((create_time)/1000/24/60/60) and status = 1;

I want to take each day's total sale value and put them on an Highcharts graph. But as you know, some months have 31 and the others have 30 days, so I can't write query for each day seperately. Can you help me with this? How can I write query for each day of a month, regarding that each month have different number of days? Thank you for your help.

Upvotes: 0

Views: 37

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

Use TO_CHAR() and aggregate on the day:

SELECT
    TO_CHAR(create_time, 'yyyy/mm/dd'),
    COUNT(*)
FROM registered_devices
WHERE status = 1
GROUP BY
    TO_CHAR(create_time, 'yyyy/mm/dd')

One caveat here is that the above query will not output a given day if it had no activity whatsoever. If you want to guarantee to always show every day, you could create a calendar table.

Also, you can limit the date range by adding another condition to the WHERE clause which checks create_time.

Upvotes: 1

Related Questions