How to automate to get the count of a record for every single day for a couple of months as result?

Question

I have a table TableA which has customer records. It also has a specific type called 'NAME'. Basically everyday couple of new customers are registered and this registration record is added into Database with 'NAME' type. What i currently need is just the count of the number of NAME referenceid's for every single day for AUG, SEP, OCT and NOV till 15th.

select count(referenceid)
from tableA
where type = 'NAME'

If I need the count for a whole month I do the following

select count(referenceid)
from tableA
where type = 'NAME'
  and TIMESTAMP BETWEEN TO_DATE('01-AUG-16') AND TO_DATE('31-AUG-16')

I'm not sure how to automatically get the counts for everyday for all the above mentioned months. If there is no automated way, I'll have to run the same query for almost 90+ times for every single day.

I would appreciate if somebody could help.

Note that the solutions provided so far using only GROUP BY DATE will only provide counts for days where data exists. If for whatever reason no new users are registered on a particular day, this would not cause a count of 0 to be displayed. If you do want to show days with 0 you would need to generate a list of days and join that accordingly - heres a useful article http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates — Chriz, Nov 15 '16 at 08:57
@Chriz I did not consider that. May I use your propose in my answer? — rbr94, Nov 15 '16 at 09:11
@rbr94 Of course, I'm not all that familiar with how to do with within MySQL, would be good to see a solution! In MSSQL its pretty easy with the use of a recursive CTE! — Chriz, Nov 15 '16 at 09:19

rbr94 · Accepted Answer · 2016-11-15T10:37:25.277

1

If I understand you correctly, try to GROUP BY each day using your TIMESTAMP.

SELECT TO_CHAR(TIMESTAMP, 'YYYY-MM-DD') AS yourDate, COUNT(referenceid) 
FROM tableA 
WHERE type='NAME' AND TIMESTAMP BETWEEN TO_DATE('01-AUG-16') AND TO_DATE('31-AUG-16')
GROUP BY TO_CHAR(TIMESTAMP, 'YYYY-MM-DD')
ORDER BY yourDate

edited Nov 15 '16 at 10:37

answered Nov 15 '16 at 08:48

rbr94

2,227
3
23
39

it says ORA-00936: missing expression and points at the first DATE(TIMESTAMP) – Sai Avinash Nov 15 '16 at 09:49
@SaiAvinash How does your `Timestamp` value look like? – rbr94 Nov 15 '16 at 09:50
TIMESTAMP -- 02/Sep/16 12:15:54 PM – Sai Avinash Nov 15 '16 at 09:52
1

By the way, you use Oracle / PLSQL?` Change your tags please – rbr94 Nov 15 '16 at 09:54
actually i'm using oracle 11g.. where do i exactly check the versions i'm using? i changed the tag to oracle 11g and sql, if that's ok.. – Sai Avinash Nov 15 '16 at 09:57
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128146/discussion-between-rbr94-and-sai-avinash). – rbr94 Nov 15 '16 at 10:02
thank you. this query gives me the result i wanted :) – Sai Avinash Nov 15 '16 at 10:39

score -1 · Answer 2 · edited Nov 15 '16 at 09:26

-1

select 
    count(referenceid),
    DATENAME(MONTH,[datetimecolumn]) 
from tableA 
where type='NAME' 
GROUP BY MONTH([datetimecolumn])

edited Nov 15 '16 at 09:26

rbr94

2,227
3
23
39

answered Nov 15 '16 at 09:03

Ankita

1

1

By the way, I don't think that fits the question. It is asked for daily values, not for the values of a whole month. And furthermore you should provide a little explanation to your proposed statement – rbr94 Nov 15 '16 at 09:07

How to automate to get the count of a record for every single day for a couple of months as result?

2 Answers2