Sai Avinash Sai Avinash - 20 days ago 7
SQL Question

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

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.

Answer

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
Comments