SQL Server Performance

SELECT Count(*) problem

Discussion in 'SQL Server 2005 General Developer Questions' started by nickm324, Jun 16, 2006.

  1. nickm324 New Member

    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.

    Completed
    22
    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
  2. FrankKalis Moderator

  3. Madhivanan Moderator

    Read about Cross-tab Reports in sql server help file. That will give you more ideas

    Madhivanan

    Failing to plan is Planning to fail
  4. nickm324 New Member

    I guess I could use a temp table, but how would that work with many users accessing the sp at the same time?
  5. FrankKalis Moderator

    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
  6. druer New Member

    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 @Completed+@Energy+@InvalidPin.. 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.
  7. mmarovic Active Member

    How about:


    select 'completed', count(*)
    from ....

    union all

    select 'energy', count(*)
    from ...
    ...

Share This Page