3

I am lost on how to solve what I believe should be a simple query.

I want to count the number of entries for each date in a table. The column DateCreated is in DateTime format. I can convert the datetime to date using 

convert(VARCHAR, JobApps.DateCreated, 2) as Date

but with COUNT(ID) as Qty I get a count of 1 with multiple "Date" rows.

Here is the SQL query I am using.

SELECT  
    convert(VARCHAR, DateCreated, 2) as Date, COUNT(CompanyName) as Qty
FROM Apps  
GROUP BY DateCreated
ORDER BY DateCreated DESC

This is the results I get.

Date       Qty
------------------
13.05.29   1
13.05.29   1
13.05.29   1
13.05.29   1
13.05.29   1
13.05.28   1
13.05.28   1
13.05.27   1
13.05.27   1

etc...

What I wanting is a result like this...

Date       Qty
-----------------
13.05.29   5
13.05.28   2
13.05.27   2

etc...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stu Riaghain
  • 35
  • 1
  • 5

1 Answers1

2

Just gotta change your GROUP BY to use the actual value you want:

SELECT convert(VARCHAR, DateCreated, 2) as Date, COUNT(1) as Qty
FROM Apps
GROUP BY convert(VARCHAR, DateCreated, 2)
ORDER BY Date DESC
zimdanen
  • 5,508
  • 7
  • 44
  • 89
  • The query Zimdanen works by using ORDER BY convert(VARCHAR, DateCreated, 2) DESC (I guessed I could do that). Thanks for the explaination, I didn't know i could use the CONVERT statement in GROUP BY. – Stu Riaghain May 30 '13 at 15:42
  • @StuRiaghain: Should be able ORDER BY [Date](http://stackoverflow.com/a/3841804/128217) DESC. – zimdanen May 30 '13 at 15:44