SQL Server Performance Forum – Threads Archive
SELECT Count(*) problem
I have a table that has a column called stopcode and need to get the counts from serveral different stopcodes that are in this column. I have done this using mutiple selects but how can I combined them in to one result set that will be returned to an ASP page. Problem 1 is it returns all the results as individual rows with out any column names. below is an extract of the code I have. I had to use dynamic sql to build the sql strings as the tablename will be different depending on what month it is. When I execute the query it returns all the results I want but as individual rows with no column names. I have used many variations on this, for example "count(*) AS completed" and so forth. when I use the AS clause it returns 1 column called completed as below and ignores all the AS clauses in the lines follwing it. Completed22
0
0
0
57
0
13 Any help would be greatly appreciated I have been working on this for a while and am getting nowhere fast.
set @Sql = @Sql + ‘select completed = Count(*) From ‘ + @sitetbl + ‘ Where completed = 1 UNION ALL’ + CHAR(13);
set @Sql = @Sql + ‘select energy = Count(*) From ‘ + @sitetbl + ‘ Where energy > 1 UNION ALL’ + CHAR(13);
set @Sql = @Sql + ‘select invalidpin = Count(*) From ‘ + @sitetbl + ‘ Where stopcode = 42 UNION ALL’ + CHAR(13);
set @Sql = @Sql + ‘select invalidpan= Count(*) From ‘ + @sitetbl + ‘ Where stopcode = 41 UNION ALL’ + CHAR(13);
set @Sql = @Sql + ‘select adminblock = Count(*) From ‘ + @sitetbl + ‘ Where stopcode = 40 or stopcode = 43 or stopcode = 53 or stopcode =54 or stopcode = 57 or stopcode = 59 UNION ALL’ + CHAR(13);
set @Sql = @Sql + ‘select notrunk = Count(*) From ‘ + @sitetbl + ‘ Where stopcode = 55 UNION ALL’ + CHAR(13);
set @Sql = @Sql + ‘select nofunds = Count(*) From ‘ + @sitetbl + ‘ Where stopcode = 51 UNION ALL’ + CHAR(13);
set @Sql = @Sql + ‘select norate = Count(*) From ‘ + @sitetbl + ‘ Where stopcode = 48’ print @sql exec Sp_executesql @Sql
Can you use a temp table and INSERT the COUNTs into that table and finally SELECT from that table? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Read about Cross-tab Reports in sql server help file. That will give you more ideas Madhivanan Failing to plan is Planning to fail
I guess I could use a temp table, but how would that work with many users accessing the sp at the same time?
That would cause no problem, because internally a local temp table is bound to the session which has created it. No other session can see such a local temp table and there will be no interferences between multiple sessions. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
For what you are doing you can also consider using the CASE command to do a single pass through the table where you just sum up the individual values and then sum up all of them. or declare variables for the returned counts, select the counts to those local variables, then have a final select that pulls back the values individually with the final one being @[email protected][email protected] as TotalCount. Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.
How about:
select ‘completed’, count(*)
from …. union all select ‘energy’, count(*)
from …
…
]]>