SQL Server Performance

IF Else within a Case statement

Discussion in 'T-SQL Performance Tuning for Developers' started by EasySQL, Feb 14, 2006.

  1. EasySQL New Member

    In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row.
    I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated.
    Please help
    Thanks

    select
    isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'') Lastname
    , case ProductID
    WHEN 22 then count(S.Product)
    Else 0
    END AS Builders
    , case ProductID
    WHEN 23 then count(S.Product)
    Else 0
    END AS Associates
    , case ProductID
    WHEN 24 then count(S.Product)
    Else 0
    END AS Affiliates
    FROM vwpersons p with (nolock)
    join vwSubscriptions S with (nolock)
    on S.RecipientID = P.ID
    where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
    group by P.Lastname, S.ProductID
    having count(P.LastName)>=1
    order by 1

    Result set :

    Firstname Builders AssociatesAffiliates

    Abarca010
    Abascal200
    Abelar100
    Abeyta100
    Abeyta010
    Abreu100
    Abreu010
    Acevedo100
    Acevedo050
  2. Adriaan New Member

    You should find a response to much the same question here on this board not more than a few days ago. It had the exact same table and column names.

    Wink wink, nudge nudge.
  3. EasySQL New Member

    Hi Adriaan
    I have worked on the query and have exact results as desired except for this one.
    would appreciate if you can take a look.No matter the table is the same but the issue is different.
    thanks
  4. Adriaan New Member

    Okay, fair enough.

    You don't seem to understand the concept of GROUP BY terribly well, though. Each distinct combination of values on the columns in the GROUP BY clause will get its own row in the resultset.
  5. EasySQL New Member

    Yeh I agree with you but in my scenario is there a alternate approach to get the count of the common name in one row.
  6. Adriaan New Member

    Not sure that this is really a new question ...

    Did you test the exact queries that we proposed in the previous thread? If so, please copy them here and explain what was wrong with the results that they gave you.
  7. EasySQL New Member

    I have simplified the queries which i was given by you and twan to the above one to get the same results but my issue here is with the case statement .I am almost there .Please take a look at the case block which i need to modify to get counts under diff category for the same name like group by name.Take a look at the resultset for a clear picture.
    thanks
  8. Adriaan New Member

    Why did you simplify the queries? What was wrong with the results from the complex queries?
  9. EasySQL New Member

    Hi this is the one i tried and it worked.<br /><br />select<br />isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname,<br />SUM(case ProductID WHEN 22 then 1 Else 0 END) AS Builders,<br />SUM(case ProductID WHEN 23 then 1 Else 0 END) AS Associates,<br />SUM(case ProductID WHEN 24 then 1 Else 0 END) AS Affiliates<br />FROM vwpersons p with (nolock)<br />join vwSubscriptions S with (nolock)<br />on S.RecipientID = P.ID<br />where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)<br />andS.ProductID in (22,23,24)<br />group by P.Lastname<br />having count(P.LastName)&gt;=1<br />order by 1<br /><br />[^][^][^][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page