CODEWITHSUNDEEP
sqlsql-serverdatetimegroup-by
Paul Hopkinson
Paul Hopkinson

Reputation: 441

SQL Count by date

I have a table that has sign ups and the date they signed up. I want to run a query that gives the output of total signups per day.

I have the following query:

SELECT COUNT(dtm_SignUpDate) as TotalSignUpsPerDay, dtm_SignUpDate as Count_Date
FROM tbl_Account
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, dtm_SignUpDate))

but get

Column 'tbl_Account.dtm_SignUpDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

what do I need to change to get this to work.

I followed this link (SQL Count for each date) but it still came up with the same error as described above.

Upvotes: 1

Views: 7576

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

How about this?

SELECT cast(dtm_SignUpDate as date) as dtm_SignUpDate, count(*) as TotalSignUpsPerDay
FROM tbl_Account
GROUP BY cast(dtm_SignUpDate as date)
ORDER BY cast(dtm_SignUpDate as date);

If dtmSignUpDate is already a date with no time component, then:

SELECT dtm_SignUpDate, count(*) as TotalSignUpsPerDay
FROM tbl_Account
GROUP BY dtm_SignUpDate
ORDER BY dtm_SignUpDate ;

Upvotes: 5

Salman Arshad
Salman Arshad

Reputation: 272006

Just use the truncated date in your select clause as-is, it is already a date with hour/minute/seconds removed:

SELECT
    COUNT(dtm_SignUpDate) AS TotalSignUpsPerDay,
    DATEADD(dd, 0, DATEDIFF(dd, 0, dtm_SignUpDate)) AS Count_Date
FROM tbl_Account
GROUP BY
    DATEADD(dd, 0, DATEDIFF(dd, 0, dtm_SignUpDate))

PS: people stopped using DATEADD(dd, 0, ...) for truncating dates long time ago. You can simply use CAST(DateTimeVal AS DATE).

Upvotes: 3

Related Questions