Select Count query

[Joelo]

Please could someone tell what I am doing wrong

strsql = "SELECT ZOO, COUNT(ZOO) AS prev FROM TJAB INNER JOIN TCAB AS B ON XCOW=B.BCUB"
strsql = strsql & " WHERE [JOB] = "&bad&" AND [BEEV] >= "&bad1&" AND [CEEV] <= "&bad2&" AND [ZOO] like 1 ORDER BY [ZOO] DESC"


Thanks in advance

 

[Chopstik]

Would you not just want to do a count of Zoo (take out the Zoo and just leave the Count(zoo))? Something as follows:

strsql = "SELECT COUNT(ZOO) AS prev FROM TJAB INNER JOIN TCAB AS B ON XCOW=B.BCUB"
strsql = strsql & " WHERE [JOB] = "&bad&" AND [BEEV] >= "&bad1&" AND [CEEV] <= "&bad2&" AND [ZOO] like 1 ORDER BY [ZOO] DESC"

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[Joelo]

I Tried

strsql = "SELECT COUNT(ZOO) AS prev FROM TJAB INNER JOIN TCAB AS B ON XCOW=B.BCUB"
strsql = strsql & " WHERE [JOB] = "&bad&" AND [BEEV] >= "&bad1&" AND [CEEV] <= "&bad2&" AND [ZOO] like 1 ORDER BY [ZOO] DESC"

and got the following error

Microsoft OLE DB Provider for SQL Server error '80040e14'

Column name 'TJAB.ZOO' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.

bad.asp, line 85

 

[Mr3Putt]

SELECT COUNT[ZOO] AS prev ...
... [gray]your other sql[/gray]
...AND [ZOO] like 1 [green]GROUP BY [ZOO][/green] ORDER BY 1

If you are going to COUNT something, you have to GROUP it... even if there's only one of it.

Semantically, it doesn't make sense to force you got GROUP things, when there's only one sort of thing, but when you're returning counts of multiple types of ZOOs, you'll need to group each ZOO-type together, in order to count it.

A pile of pennies, a pile of nickles, a pile of pocket lint. Much easier to calculate when they're grouped.

 

[Joelo]

Thanks..it worked like a charm

Please I am trying to count zoo like 1 and zoo like 2 and zoo like and display the results all in page... please any idea


strsql = "SELECT COUNT(ZOO) AS prev FROM TJAB INNER JOIN TCAB AS B ON XCOW=B.BCUB"
strsql = strsql & " WHERE [JOB] = "&bad&" AND [BEEV] >= "&bad1&" AND [CEEV] <= "&bad2&" AND [ZOO] like 1 GROUP BY [ZOO] ORDER BY [ZOO] DESC"

 

[Mr3Putt]

Two things:

First, add a bit to your SELECT statement to:
"SELECT COUNT(ZOO) AS prev, ZOO FROM..."

So, you're selecting both the COUNT of the ZOOs AND you're showing WHICH ZOO is being counted.

Second, remove the "AND [ZOO] like 1" portion of your query string. That will allow ALL ZOO-types into the results, then it will group them, then it will return a count of each one.

So, you should end up with a result like:

prev ZOO
5 12
4 8
3 125
2 563
1 3

...or something to that effect.

Good luck on your project.