ORDER BY month problem 1

[jaydeebe]

I have a query as follows -

SELECT Format([StartDate],"mmm") AS Month, Count(*) AS Bookings
FROM tblbookings
WHERE (((tblbookings.StartDate) Between #12/31/2004# And #12/31/2005#))
GROUP BY Format([StartDate],"mmm");

I want to order the results by month but when I try the list is in alphabetical order instead of month order. Can anyone help me?

 

[PHV]

SELECT Format([StartDate],"mmm") AS Month, Count(*) AS Bookings
FROM tblbookings
WHERE (((tblbookings.StartDate) Between #12/31/2004# And #12/31/2005#))
GROUP BY Format([StartDate],"mmm")
ORDER BY [StartDate];

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

 

[dhookom]

Try:

SELECT Format([StartDate],"mmm") AS Month, Count(*) AS Bookings
FROM tblbookings
WHERE (((tblbookings.StartDate) Between #12/31/2004# And #12/31/2005#))
GROUP BY Format([StartDate],"mmm")
ORDER BY Month([StartDate]);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jaydeebe]

PHV - I tried that and i get the message - You tried to execute a query that doesn't include the specified expression '[StartDate]' as part of an aggregate function.

Duane - I tried that and i get the message - You tried to execute a query that doesn't include the specified expression 'Month([StartDate])' as part of an aggregate function.

 

[dhookom]

SELECT Format([StartDate],"mmm") AS Month, Count(*) AS Bookings, Month([StartDate]) as MthNum
FROM tblbookings
WHERE (((tblbookings.StartDate) Between #12/31/2004# And #12/31/2005#))
GROUP BY Format([StartDate],"mmm")
ORDER BY Month([StartDate]);


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jaydeebe]

Sorry I still get the same message.

 

[r937]

add , Month([StartDate]) to the GROUP BY in Duane's last query

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[jaydeebe]

Genius! thankyou everyone!

 

[jaydeebe]

BTW r937 can you explain how that works. I don't get how grouping by makes the difference.

SELECT Format([StartDate],"mmm") AS Month, Count(*) AS Bookings
FROM tblbookings
WHERE (((tblbookings.StartDate) Between #12/31/2004# And #12/31/2005#))
GROUP BY Format([StartDate],"mmm"), Month([StartDate])
ORDER BY Month([StartDate]);

 

[r937]

sure, no prob

Format([StartDate],"mmm") is a 3-char string, while Month([StartDate]) is a number

the optimizer, clever as it is, doesn't realize they mean the same thing, it just recognizes that they are not exactly the same thing, nor are they literal values (which do not have to be in the GROUP BY), so both of them have to be in the GROUP BY

thus SELECT A,3 FROM ... GROUP BY A is okay

but SELECT A,B FROM ... GROUP BY A is not okay



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)