Help with COUNT(*)

Dear SQL,

I want to count the number of records, so I tried this:

SELECT COUNT(*) AS RecordCount
FROM Categories
WHERE Active = 1
ORDER BY Show_Order ASC

But it gives me an error:
error 8126: Column name 'Categories.Show_Order' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.

How can I make it work ? (I must ORDER it...)SELECT COUNT(*) will return a single row with a single column containing an integer value. What are you expecting an ORDER BY to sort??

Terri|||As the error says, you cant do a count without a group. Here is my suggestion:

SELECT COUNT(fieldname) AS RecordCount FROM Categories WHERE active = 1 GROUP BY fieldname ORDER BY show_order

Instead of counting all columns just use one field. A Count requires a grouping even if there isnt anything to group. For instance you have a field called id that is a primary key. Group by. Order is ALWAYS at the end and the default is ASC so no need for ASC.|||As the error says, you cant do a count without a group.
That's not accurate. You can certainly do a COUNT without explicitly giving a grouping.

What the error is saying is that if you want use an ORDER BY clause, the expression being ordered by must exist in the resultset. With this in mind, the example you've given will not work because show_order does not exist in the resultset.

And, in order to add an expression to the resultset using an aggregate function such as COUNT, you need a GROUP BY clause.

In this case, this is likely what is needed but I am not sure because more information is needed from the original poster:

SELECT Show_Order, COUNT(*) AS RecordCount
FROM Categories
WHERE Active = 1
GROUP BY Show_Order
ORDER BY Show_Order ASC

And also, I think that explicitly indicating the sort direction ("ASC") is good practice because you never know when default behaviors might change. But that's a personal preference. :-)

Terri|||Hello again & thank god 4 this forum :-)

I have now succeeded in returning the number of records
but as Terri said - when I use GROUP BY it seems to limit my recordset to only one record,

I need this SP to be very efficient, so I like to SELECT only once, as U can see on the SP (below),
right now I use another SELECT at the end to determine the number of records to return...

Please let me know how to improve it or how to COUNT the records on the first selection
Thanks in advanced, Yovav.

/*================================================================================*/
/* Get categories (All / Titles / Subtitles) */
/*================================================================================*/
CREATE PROCEDURE Admin_Categories_Get
/*
' Usage example:
' ~~~~~~~~~~~
AdoCmd.CommandType = adCmdStoredProc
AdoCmd.CommandText = "Admin_Categories_Get"
' Return parameter comes first and can be used after recordset is closed
AdoCmd.Parameters.Append AdoCmd.CreateParameter("RETURN", adInteger, adParamReturnValue, 4)
' 0 (All), 1 (All active) 2 (All active compact), 10 (All titles), 11 (All active titles), 20 (All subtitles), 21 (All active subtitles)
AdoCmd.Parameters.Append AdoCmd.CreateParameter("@.ShowType", adTinyInt, adParamInput, 1, 0)
Set CategoriesRS = AdoCmd.Execute
CategoriesRS.Close
Response.Write("Return value = " &CStr(AdoCmd.Parameters.Item("RETURN").Value))
*/
@.ShowType tinyint
AS
DECLARE @.RecordCount int
IF @.ShowType = 0 -- (All)
SELECT *
FROM Categories
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 1 -- (All active)
SELECT *
FROM Categories
WHERE Active = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 2 -- (All active compact)
SELECT Category_ID, Title, Name_Eng, Name_Heb
FROM Categories
WHERE Active = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 10 -- (All titles)
SELECT *
FROM Categories
WHERE Title = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 11 -- (All active titles)
SELECT *
FROM Categories
WHERE Active = 1 /* True */ AND Title = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 20 -- (All subtitles)
SELECT *
FROM Categories
WHERE Title = 0 /* False */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 21 -- (All active subtitles)
SELECT *
FROM Categories
WHERE Active = 1 /* True */ AND Title = 0 /* False */
ORDER BY Show_Order ASC
-- Count *ALL* records on table Categories
SELECT @.RecordCount = COUNT(*) FROM Categories
RETURN @.RecordCount
GO

|||...

oh dear goodness.

In the spirit of the holidays..

Why don't you just send in Active and Title as parameters? It appears to me that they're bits, and two bits are smaller in size than one integer.|||it wont help, coz sometimes I need to do things according to the ShowType
+
my main problem was how to return the COUNT of records together with the recordset...