SQL Server Performance

SQL Statement

Discussion in 'General Developer Questions' started by sabst79, May 29, 2003.

  1. sabst79 New Member

    Hi,
    Im not sure if this is the right forum for this, but here goes.

    i have a database table w/ fields "Zone" and "Status"
    Zone can be CF1, CF2, or CF3 Status is 0,1,2,3, or 4

    now i need to do a count of each status for each zone, but only have the zone appear once, something that looks like this:

    Zone Status = 0 Status = 1 Status = 2 Total Lines(sum of status 1,2,3) %(s0/total)
    CF1 45 55 66 166 45/166 * 100
    CF2 33 23 35 91

    i have this query to try to try to combine just two status values, 0 and 4:
    SELECT (SELECT DISTINCT WHZone) AS Zone, (Select Count(Status) where status = '0') as untouched, (SELECT Count(Status) where tblPick.Status = '4') AS Short
    FROM tblPick
    GROUP BY WHZone, status having status = '0' or status = '4'

    but what that results in is this:
    zone untouched short
    CF2 138 NULL
    CF3 156 NULL
    CF1 NULL 108
    CF2 NULL 92
    CF3 NULL 68
    CF1 160 NULL

    how can i combine these two columns, so each zone only appears once?

    thanks for all your help!!!

  2. Chappy New Member

    For your example, the reason you are getting NULLs is thus..
    For CF2, lets say, you are getting one row for status='0' (and on that row the count(Status) where status='4' is obviously NULL). And then you get another row for status='4' (and likewise the count(Status) where status='0' is NULL also).

    I must admit I dont really understand what youre trying to do in your first explanation, but if all you want is a count of each Zone, broken down by status, where the status is one of a few values... try this

    select Zone, Status, count(Status)
    where Status in ('0', '4')
    group by Zone, Status

    Some extra advice..

    Dont use the HAVING clause for any criteria which can be done in the WHERE clause. HAVING is carried out as the last stage and is therefore less efficient (sql must process more data in the intermediate stages of the execution plan).

    Try to avoid using derived tables unless really necessary. They are sometimes very useful for plucking out individual values, but as you have discovered, it can be very difficult to link them back into the context of the main query structure.

    Read a little more about aggregates, and the behavior of them when used with the Group BY clause. SQL is a powerful language. If what you want to do seems like it should be simple, it very often is.

    Also im not sure what you mean by 'Total Lines(sum of status 1,2,3) %(s0/total)', but you may want to also read about the COMPUTE and COMPUTE BY clauses.

    Hope this is of use



  3. sabst79 New Member

    Chappy,

    Thanks for the help, what im trying to do is combine status 0 and status 4 for each zone, so each zone only appears once.

    zone status 0 status4

    and have a count for each zone of how many times each status appears in that zone

    zone status0 status4
    cf1 23 45
    cf2 67 34

    something like that. i understand why im getting the null values and im trying to combine the two columns so the zones dont appear more than once. the total is just the sum of the counts so the total for cf1 would be 23+45 and % is just 23/total column, which i think i know how to do, its just getting the status and zone columns to come up the way i want them too.

    Thanks for your help!
  4. vbkenya New Member

  5. Logicalman New Member

    sab,
    Here's a simple procedure that will do what you need. It can handle unlimited Zones, but you will need to add extra lines for further Status numbers (in the while loop). It avoids using cursors, and emulates a pivot table, just copy and paste it into an empty sp window, save and execute it.

    Hope it helps,

    ***** START CODE *****


    -- A D Scott
    -- 06/03/2003
    -- for sabst79

    --This procedure counts the number of instances of any Zone reference by Status
    CREATE PROCEDURE [dbo].[sp_TEST] AS

    --# CREATE TEMP TABLE OBJECTS
    CREATE TABLE #ZoneRef (aZone varchar (5), flagme int)
    CREATE TABLE #ZoneTest (aZone varchar (5), S1 int, S2 int, S3 int, S4 int, S5 int)

    --# DECLARE LOCAL VARIABLE
    declare @@ZoneRef varchar (5)-- Holds the Zone ID reference

    --# GET DISTINCT LIST OF ZONES
    INSERT INTO #ZoneRef (aZone, flagme) (SELECT DISTINCT [Zone], 0 FROM dbo.test)

    --# GET THE FIRST ZONE REF
    set @@ZoneRef = (SELECT TOP 1 aZone FROM #ZoneRef WHERE flagme = 0)
    --# LOOP THROUGH ZONE REFS TABLE
    WHILE (@@ZoneRef IS NOT NULL)
    BEGIN
    INSERT INTO #ZoneTest (aZone, S1,S2,S3,S4,S5) (SELECT @@ZoneRef, 1,0,0,0,0 FROM dbo.test WHERE Status = 1 AND [Zone] LIKE @@ZoneRef)
    INSERT INTO #ZoneTest (aZone, S1,S2,S3,S4,S5) (SELECT @@ZoneRef, 0,1,0,0,0 FROM dbo.test WHERE Status = 2 AND [Zone] LIKE @@ZoneRef)
    INSERT INTO #ZoneTest (aZone, S1,S2,S3,S4,S5) (SELECT @@ZoneRef, 0,0,1,0,0 FROM dbo.test WHERE Status = 3 AND [Zone] LIKE @@ZoneRef)
    INSERT INTO #ZoneTest (aZone, S1,S2,S3,S4,S5) (SELECT @@ZoneRef, 0,0,0,1,0 FROM dbo.test WHERE Status = 4 AND [Zone] LIKE @@ZoneRef)
    INSERT INTO #ZoneTest (aZone, S1,S2,S3,S4,S5) (SELECT @@ZoneRef, 0,0,0,0,1 FROM dbo.test WHERE Status = 5 AND [Zone] LIKE @@ZoneRef)
    UPDATE #ZoneRef SET flagme = 1 WHERE aZone LIKE @@ZoneRef
    set @@ZoneRef = (SELECT TOP 1 aZone FROM #ZoneRef WHERE flagme = 0)
    END

    --DEBUG PRINT
    --SELECT [Zone], Status FROM dbo.test
    --SELECT * FROM #ZoneTest

    --# OUTPUT
    select aZone, SUM(S1) AS S1, SUM(S2) AS S2, SUM(S3) AS S3, SUM(S4) AS S4, SUM(S5) AS S5
    from #ZoneTest GROUP BY aZone

    --# DROP ALL TABLE OBJECTS CORRECTLY
    DROP TABLE #ZoneRef
    DROP TABLE #ZoneTest
    GO

    ***** END CODE *****


  6. Logicalman New Member

    sabst79,
    Just another note:
    ZONE is a ODBC Ketword, and as such, it is not adviseable to use any keywords as Column names or aliases, as it will require you to use square brackets either side to stop errors when executing your code.
    A list of such Keywords can be found in BOL, or, by just typing the word as a column name in the new table GUI in Ent Mgr.

Share This Page