Count Records by month 3

[sbbrown9924]

I am at the point in learning SQL/ VBscript where I need to combine both into a query. I am writing a query to count the number of patient records per month. Instead of writing 12 separate queries to count / month, how could I write this into a single query? Thanks.

 

[PHV]

A starting point:
SELECT Month([date field]) AS theMonth, COUNT(*) AS NumberOfPatients
FROM yourTable
GROUP BY Month([date field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sbbrown9924]

PHV;

Cool - that worked great. Just for learning purposes, is there a way to write a loop to do the same thing?

 

[PHV]

A loop in SQL ????

 

[sbbrown9924]

Well, SQL embedded in a loop?

 

[SkipVought]

BTW, if your dates span more than on year, you will need to include the year & month, something like...

SELECT Format([date field],"yyyymm") AS YearMonth, COUNT(*) AS NumberOfPatients
FROM yourTable
GROUP BY Format([date field],"yyyymm")

so if you only want data for 2005, for instance...

SELECT Format([date field],"yyyymm") AS YearMonth, COUNT(*) AS NumberOfPatients
FROM yourTable
Where Year([date field])=2005 
GROUP BY Format([date field],"yyyymm")

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[sbbrown9924]

Thanks Skip!

 

[Melagan]

Format([Date_Field], "mmm yyyy") gives a nice looking read-out.
Jan 2006
Feb 2006
Mar 2006
etc.



~Melagan
______
"It's never too late to become what you might have been.

 

[sbbrown9924]

Melagan;

Cool. That makes it much more presentable. I found that it was sorting by the alpha value of the first character of month abbreviation, so I altered the query so it would sort by year first, then the numeric value of the month, but display the month in abbreviated text:

SELECT Format(TriageTime,'mmm-yyyy') AS YearMonth, Format(TriageTime, 'yyyy-mm') AS Mth, COUNT(*) AS NumberOfPatients
FROM Archive GROUP BY Format(TriageTime,'yyyy-mm'),Format(TriageTime, 'mmm-yyyy')

Thanks!

 

[Melagan]

I use the Format([datefield], "yyyymm") to Sort my records (but not show in the query) and Format([datefield], "mmm yyyy") for the actual display all the time =) I have to give props to PHV for showing that trick to me a while back


~Melagan
______
"It's never too late to become what you might have been.

 

[sbbrown9924]

Nice trick - Kudos to PHV too! I also had to sort by day of the week, so I used:

SELECT Format(TriageTime,'dddd') AS AlphaDayOfWeek, Format(TriageTime,'w') AS NumbericDayOfWeek,COUNT(*) AS NumberOfPatients FROM Archive GROUP BY Format(TriageTime,'w'), Format(TriageTime,'dddd')