Using Count

Question

Using Count

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
April 1, 2008 at 8:37 am

#183933

I am trying to get a count of records per DateImport column. My code so far:
Select Track_NO, Status, [Date],DateIMPORT, COunt(*) AS RecordCount
from ClippershipImportJan2007
GROUp BY Track_NO, Status,[Date],[DateImport]
ORDER BY DATEImport
But this just returns a count for every record. Please advise.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Look at your dateimport. They include times, so you are likely getting a "count" for each group of the time, not the date.

If you need it by date, then extract out the date components using datepart, i.e.

datepart( yyyy, dateimport) 'year',

datepart(mm, dateimport) 'month)

in both the column list (SELECT part) and the GROUP BY. You should get the groups then by the date. The above two items would group by month (and year). You can add day.

Don't forget to remove the base DATEIMPORT column.

GSquared SSC Guru Points: 260824 More actions · Answer 2

I'm going to hazard a guess that [Date] and/or [DateImport] are datetime columns with the date AND the time in them. In which case, your Group By is almost certainly going to end up with one row per entry in the main table, unless by amazing coincidence, two or more records were done within .003 seconds of each other.

If that's the case, you need to break out the date without the time on it for your Select and Group By clauses. "cast(floor(cast([Date] as float)) as datetime)" will do that. (There are other ways, they all work about the same. Another is "convert(varchar(25), [Date], 1)". I've tested, and they have the same performance.)

If you have to run queries like this a lot, then calculated, indexed fields in the table you query can really speed the queries up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 3

Thanks a lot, the time was messing me up. Corrected that and it works fine.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!