SQL Server Performance

Query Help Sum Distinct Rows

Discussion in 'ALL SQL SERVER QUESTIONS' started by StevieB, Sep 16, 2013.

  1. StevieB New Member

    I have a query

    Code:
    declare @Total int
    declare @Accepted int
    declare @Inprogress int
    
    select  @Total=count(*),
            @Accepted=sum(case LastState WHEN 0 THEN 0 ELSE 1 end),--LastStateName No Status
            --@InProgress=sum (case LastState WHEN 8 THEN 0 else 1 end)--case LastState WHEN IN (7,8,10,12) THEN 2 end),
              @InProgress=count(case WHEN LastState IN (7,8,10,12) THEN 0 end)
                --when 7 then 2 LastStateName Waiting
                --when 8  then 2 LastStateName Running
                --when 10 then 2 LastStateName Retrying
                --when 12 then 2 LastStateName Reboot Pending
                --end)
    from v_ClientAdvertisementStatus
    where AdvertisementID='XXX20549'
    select LastAcceptanceStateName as C013, count(*) as 'Number of Resources',
        ROUND(100.0*count(*)/@Total,1) as C016,
    AdvertisementID
    from v_ClientAdvertisementStatus
    where AdvertisementID='XXX20549'
    group by LastAcceptanceStateName, AdvertisementID
    
    select LastStateName as C017, count(*) as 'Number of Resources',
          ROUND(100.0*count(*)/@Accepted,1)  as C016,
    AdvertisementID
    from v_ClientAdvertisementStatus
    where AdvertisementID='XXX20549'
    and LastState!=0
    group by LastStateName, AdvertisementID
    
    that produces the following results.

    Last Acceptance Number of Resources C016 AdvertisementID
    No Status 271 2.600000000000 XXX20549
    Accepted 10330 97.400000000000 XXX20549

    Last State Name Number of Resources C016 AdvertisementID
    Failed 28 0.300000000000 XXX20549
    Succeeded 9944 96.300000000000 XXX20549
    Waiting 276 2.700000000000 XXX20549
    Retrying 1 0.000000000000 XXX20549
    Accepted - No Further Status 81 0.800000000000 XXX20549

    I am trying to adjust the query so that I can get a row that shows a similar result but would sum of LastStateName.Waiting and LastStateName.Retrying and place it under titled In Progress. The results would like. Is this possible? I have tried several options (Group by, Having, Pivot, Case)but I cant seem to get the code correct. Any help would be most appreciated.

    Last Acceptance Number of Resources C016 AdvertisementID

    No Status 271 2.600000000000 XXX20549

    Accepted 10330 97.400000000000 XXX20549

    Last State Name Number of Resources C016 AdvertisementID

    Failed 28 0.300000000000 XXX20549

    Succeeded 9944 96.300000000000 XXX20549

    InProgress 277 2.700000000000 XXX20549

    Accepted - No Further Status 81 0.800000000000 XXX20549
  2. davidfarr Member

    In your final query; you might be able to include a CASE condition in your GROUP BY clause, something like this;
    Code:
    select case when LastState in (7,10) then 'InProgress' else LastStateName end as LastStateName, count(*) as 'Number of Resources',
    ROUND(100.0*count(*)/@Accepted,1)  as C016,
    AdvertisementID
    from v_ClientAdvertisementStatus
    where AdvertisementID='XXX20549'
    and LastState!=0
    group by case when LastState in (7,10) then 'InProgress' else LastStateName end, AdvertisementID
    
  3. StevieB New Member

    This works perfectly. Thanks for you help. I clearly didn't understand how to use group by.
    Thank You, Thank You, Thank You

Share This Page