SQL and Count

[mastermind455]

Hi all

I use the SQL Statement below to query a customer database

sql="SELECT COUNT(TLD) AS Anzahl FROM [Kunden] WHERE [Kunden].[TLD] IS NOT NULL UNION SELECT * FROM [SELECT count(*) AS Anzahl,TLD From [Kunden] WHERE TLD IS NOT NULL GROUP BY TLD] AS Subquery ORDER BY Anzahl DESC"


Now the following Error appears in the browser:


Falsche Syntax in der Nähe von ']'.

(wrong syntax near to ]

whats wrong?

thanks for your help

best regards

E.Altherr

 

[r937]

this is microsoft access, right? the question would be better asked in the access forum

your query originally had parentheses ( ) and not square brackets [ ], correct? until you saved the query?

try a period after the closing square bracket

GROUP BY TLD]. AS Subquery

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

 

[JamesLean]

There also seems to be a problem in that the first query in the union has one column whereas the second query has two?

--James

 

[r937]

whups, i missed that, and it's so obvious ;-)

it seems the intent of the union is to get the overall count, plus the counts per TLD

mastermind, change the start of the query to this --

SELECT COUNT(TLD) AS Anzahl, 'Alle ' as TLD FROM ...

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

 

[PHV]

You wanted this ?
sql="SELECT COUNT(*) AS Anzahl,TLD FROM Kunden WHERE TLD IS NOT NULL GROUP BY TLD UNION SELECT COUNT(*),'TOTAL' FROM Kunden WHERE TLD IS NOT NULL ORDER BY 1 DESC"

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

 

[JamesLean]

You could also use:

SELECT COUNT(*) AS Anzahl, TLD
FROM Kunden
WHERE TLD IS NOT NULL
GROUP BY TLD [COLOR=red]WITH ROLLUP[/color]

But I'm not sure if that's ANSI SQL? Certainly works on SQL Server.

--James

 

[dnoeth]

In SQL:1999 it's:

SELECT COUNT(*) AS Anzahl, TLD
FROM Kunden
WHERE TLD IS NOT NULL
group by rollup(tld)

or

group by grouping sets((tld), ())


And to get rid of the NULL for the total count


SELECT
case when grouping(TLD) = 1
then '-ALL-'
else TLD
end AS Anzahl
,TLD
FROM Kunden
WHERE TLD IS NOT NULL
group by rollup(tld)

Dieter

 

[mastermind455]

@r937 and @JamesLean

both querys outputs the following errors

Item cannot be found in the collection corresponding to the requested name or ordinal.

here is the rest of the code

-----------------------------------------------------------



Set RS= Server.CreateObject("ADODB.RecordSet")
sql="SELECT COUNT(*) AS Anzahl,TLD FROM [Kunden] WHERE TLD IS NOT NULL GROUP BY TLD UNION SELECT COUNT(*),'TOTAL' FROM [Kunden] WHERE TLD IS NOT NULL ORDER BY 1 DESC"
gefunden = RS("Anzahl")

rs.Close
Set rs = Nothing %>


-----------------------------------------------------------

 

[PHV]

Your code is incomplete as RS isn't opened nor browsed.

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

 

[mastermind455]

ok i tried the select statement within the SQL Query Analyzer and it works:

SELECT COUNT(*) AS 'Anzahl', TLD FROM [data].[dbo].[Kunden] WHERE TLD IS NOT NULL GROUP BY TLD

@PHV what do you mean with code incomplete?

 

[PHV]

I mean that gefunden = RS("Anzahl") will always raise an error if RS isn't opened.

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

 

[mastermind455]

ok all right

found the mistake

this line was missing

RS.Open sql,conn

now it works

 

[mastermind455]

very strange

if use the sql statement within the query analyzer
it works fine and shows me the following datas^

on the left colum it shows the amount and the next colum shows the TLD :
---------------------------------------------------------
1 .it
34 .org
52 .net
----------------------------------------------------------
and so on

but if tried this query within the asp page it shows
---------------------------------------------------------
21 Orders sorted by TLD
----------------------------------------------------------


question: do i need a loop to check every record and generate a separate entry ?

 

[PHV]

Yes, reread my post about your incomplete code (nor browsed).
Take a look at the BOF, EOF, MoveNext, ... properties/methods of the Recordset object.

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