SQL Server Performance

How to count Rows with datatype varchar

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

  1. EasySQL New Member

    I have the following scenario
    In my select query i have to make a count of the 3,4 column which is varchar dataype.
    I have to do a group by and make a total of similar Recordtypes,similar ProductID in the corresponding columns 5,6 and then the total of column 5,6 in the last column.
    Thanks to those dedicated solution providers.
    query :
    select Firstname
    , lastname
    , RecordType
    , ProductID
    , RecordTypeCount
    , ProductIDCount
    , TOTALCount
    FROM Emp
  2. Madhivanan Moderator

    If you include all columns in the group by you cant get actual count
    Post some sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail
  3. FrankKalis Moderator

  4. EasySQL New Member

    Let me put the query again
    select surname
    , ASSOCIATE
    , AFFILIATE
    , Builders
    , TOTALCount
    FROM Emp

    I want the results in the following format

    surname ASSOCIATE AFFILIATE Builders TOTALCount
    ORELLY 1 2 0 3
    DELL 0 6 2 8
    Perry 0 9 3 12

    The above results mean that with the surname Orelly there is 1 Associates , 2 AFFILIATEs ,0 builders
    and with Dell there are 6 affiliates, 2 builders.With Perry there are 0 Associates,9 affiliates,3 builders.
    I hope this is clear.Please provide with the solution.
    Thanks

  5. Adriaan New Member

    In SQL 2005 you will be able to do this in a cross-tab query, in SQL 2000 and earlier you'll just have to do it with subqueries.

    Assuming you have BIT columns on your Emp table for Associate, Affiliate and Builder, this should do the trick:

    SELECT E.SurName,
    (SELECT COUNT(*) FROM Emp t WHERE t.Surname = E.Surname AND t.Associate = 1) AssociateCount,
    (SELECT COUNT(*) FROM Emp t WHERE t.Surname = E.Surname AND t.Affiliate = 1) AffiliateCount,
    (SELECT COUNT(*) FROM Emp t WHERE t.Surname = E.Surname AND t.Builder = 1) BuilderCount,
    (SELECT t.Associate + t.Affiliate + t.Builder FROM Emp t WHERE t.Surname = E.Surname AND (t.Associate = 1 OR t.Affiliate = 1 OR t.Builder = 1)) TotalCount
    FROM
    (SELECT DISTINCT Emp.Surname FROM Emp) E
  6. Twan New Member

    Hi ya,

    alternatively you could change Adriaan's query into



    SELECT SurName,
    sum( case associate when 1 then 1 else 0 end ) AssociateCount,
    sum( case affiliate when 1 then 1 else 0 end ) AffiliateCount,
    sum( case builder when 1 then 1 else 0 end ) BuilderCount,
    sum( case associate when 1 then 1 else 0 end +
    case affiliate when 1 then 1 else 0 end +
    case builder when 1 then 1 else 0 end ) TotalCount
    FROM
    Emp
    group by
    Surname


    if you do use the bit datatype and it is non null, then you could replace the case statement with just the column name, but I'm assuming you have some other way of distinguishing the different types

    Cheers
    Twan
  7. EasySQL New Member

    Here is the original Query I have come up with.But when
    I try to sum up the total it throws me a error because i am actually
    using the ailas of the columns.Also when i try the compute i am unable to do it because the
    columns( ASSOCIATE, AFFILIATE, Builders, TOTAL) does not exists in the table and i am generating it in the fly.Hence i am unable to do a order by on it.I need the totalwhich is sum of ASSOCIATE, AFFILIATE, Builders columns , also need a sum of the total column in the bottom.
    Thanks for helping me out Adriaan,Twan,FrankKalis,Madhivanan and all the dedicated solution providers.
    here is the query


    select
    isnull(replace(Ltrim(Rtrim(P.Firstname)),',',''),'') Firstname
    , isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'') lastname
    , RecordType = isnull(replace(Ltrim(Rtrim(S.Product)),',',''),'')
    , S.ProductID
    , count(S.Product) as ProductCount
    , case ProductID
    WHEN 22 then count(S.Product)
    WHEN 23 then 0
    WHEN 24 then 0
    END AS Builders
    , case ProductID
    WHEN 23 then count(S.Product)
    WHEN 22 then 0
    WHEN 24 then 0
    END AS Associates
    , case ProductID
    WHEN 24 then count(S.Product)
    WHEN 23 then 0
    WHEN 22 then 0
    END AS Affiliates
    , SUM (Builders + Associates + Affiliates) AS TOTAL
    FROM vwpersons p with (nolock)
    join vwSubscriptions S with (nolock)
    on S.RecipientID = P.ID
    where (p.firstname in
    (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
    or P.Lastname in
    (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H))
    andS.ProductID in (22,23,24)
    AND S.StatusID in(1,2,3,11,12)
    --and P.Firstname not like 'Ron'
    group by S.Product,S.ProductID ,P.Firstname,P.Lastname , ProductID
    having count(S.Product)>1
    having count(S.Product)>1
    order by 5
    COMPUTE SUM(5) BY 5
  8. Twan New Member

    What about

    select
    isnull(replace(Ltrim(Rtrim(P.Firstname)),',',''),'') Firstname
    , isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'') lastname
    , RecordType = isnull(replace(Ltrim(Rtrim(S.Product)),',',''),'')
    , S.ProductID
    , count(S.Product) as ProductCount
    , sum( case ProductID
    WHEN 22 then count(S.Product)
    WHEN 23 then 0
    WHEN 24 then 0
    END ) AS Builders
    , sum( case ProductID
    WHEN 23 then count(S.Product)
    WHEN 22 then 0
    WHEN 24 then 0
    END ) AS Associates
    , sum( case ProductID
    WHEN 24 then count(S.Product)
    WHEN 23 then 0
    WHEN 22 then 0
    END ) AS Affiliates
    --, SUM (Builders + Associates + Affiliates) AS TOTAL
    -- let's get it to work without the TOTAL first
    FROM vwpersons p with (nolock)
    join vwSubscriptions S with (nolock)
    on S.RecipientID = P.ID
    where (p.firstname in
    (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
    or P.Lastname in
    (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H))
    and S.ProductID in (22,23,24)
    AND S.StatusID in(1,2,3,11,12)
    --and P.Firstname not like 'Ron'
    group by S.Product,S.ProductID ,P.Firstname,P.Lastname , ProductID
    having count(S.Product)>1

    order by 5

Share This Page