Counting Rows for Multiple Dates

1

HI SQL Team,

I'm trying to Count Rows for multiple Dates. The trick is that I do not have the clearance to create a new Table.

I've gotten here:
SELECT
COUNT (*)
FROM SampleTable a
WHERE a.Name = 'Charles'
AND a.Date = '07-26-2016'

My goal would be the number of 'Charles' occurrences there are on each date from the past two years. My hope is to come to something like this:

07-26-2016 13
07-25-2016 17
07-24-2016 12
...
07-25-2014 15
07-26-2014 14

Even only having the right column (values), would be great

Thank you for any help!

Best,
E46 Novice

2 
SELECT
	a.Date,
	COUNT(*)
FROM
	SampleTable a
WHERE
	a.Name = 'Charles'
	AND a.Date >= '20160101'
	AND a.Date < '20160726'
GROUP BY
	a.Date
ORDER BY 
	a.Date;

There is one issue with this query. If there is a date on which there were no rows for Charles, that date would not show up in the result. If you want that date to show with count =0, query needs to be modified.

1 Like
4

Many thanks for that JamesK; that was super helpful =)

5

SELECT
a.Name,
a.Date,
COUNT () AS Date_Count
FROM SampleTable a
WHERE a.Name IN ( 'Charles' /,'Dave', ...*/ )
AND a.Date >= '20160101' --thru current day, implicitly
GROUP BY
a.Name,
a.Date