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...

Help variable in SP

Dear All

CREATE PROCEDURE test AS
declare @.tableName varchar(100)
set @.tableName='ABCD'

INSERT INTO @.tableName
(A, B, C, E, F,G,H)
SELECT
A, B, C, D,
COUNT(*) AS G,
CONVERT(varchar(10), RecDate, 121), Left(TransID, 2) AS J
FROM
dbo.EFG
GROUP BY

ORDER BY
XXXX

( error INSERT INTO @.tableName, Do not find @.tableName, must be declare.)

help thanks

William

It appears that you first create @.TableName as a scalar variable, and then you attempt to insert into it as though it were a table variable.

If that properly reflects your intentions, I suggest that you first declare @.TableName as a table variable, with the columns properly defined -then your insert should work.

|||

You are trying to insert the values on the table which is given on the runtime. Typically it is a dynamic insert query.

Use the following query..

Code Snippet

CREATE PROCEDURE test AS

Declare @.tableName varchar(100)

Set @.tableName='ABCD'

Exec ('INSERT INTO ' + @.tableName + '(A, B, C, E, F,G,H)

SELECT

A, B, C, D,

COUNT(*) AS G,

CONVERT(varchar(10), RecDate, 121), Left(TransID, 2) AS J

FROM

dbo.EFG

GROUP BY XXXX

ORDER BY XXXX');

|||How to do?|||Thanks all|||

CREATE PROCEDURE test AS
declare @.tableName varchar(100)
set @.tableName='ABCD'

INSERT INTO @.tableName
(A, B, C, E, F,G,H)
SELECT
A, B, C, D,
COUNT(*) AS G,
CONVERT(varchar(10), RecDate, 121), Left(TransID, 2) AS J
FROM
dbo.EFG
GROUP BY

ORDER BY
XXXX

NOTE: You MUST supply the correct datatypes below. This is a suggested format to DECLARE a table variable and then populate that table variable.

Code Snippet

CREATE PROCEDURE dbo.MyTestProcedure
AS
BEGIN
DECLARE @.MyTable table
( ColA datatype,
ColB datatype,
ColC datatype,
ColD datatype,
ColE datatype,
ColF datatype,
ColG datatype
)

INSERT INTO @.MyTable
SELECT Col1,
Col2,
Col3,
Col4,
COUNT(*),
CONVERT(varchar(10), RecDate, 121),
Left(TransID, 2)
FROM dbo.EFG
GROUP BY
ORDER BY

SELECT *
FROM @.MyTable

END

help Simple question

Dear all,
I need to do some analysis on particular records.
For that I have one Table named EVENT in which I have a field named CODE.
From that field value I can get many entries with same CODE value.
Is there a way to extract the value of CODE field which occurs more often in
a table ?
thanks
regards
SergeHi
create table #test
(
col int not null primary key,
code int
)
insert into #test values (1,100)
insert into #test values (2,100)
insert into #test values (3,200)
insert into #test values (4,100)
insert into #test values (5,200)
insert into #test values (6,800)
select top 1 code,count(*)as occur from #test
group by code
order by occur desc
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:2FC5F7A1-C1F4-4033-82B9-8F84EF8FFDD7@.microsoft.com...
> Dear all,
> I need to do some analysis on particular records.
> For that I have one Table named EVENT in which I have a field named CODE.
> From that field value I can get many entries with same CODE value.
> Is there a way to extract the value of CODE field which occurs more often
> in
> a table ?
> thanks
> regards
> Serge