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

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

Agree with Madhivanan. If his answer doesn't give you an idea, please provide more information. In that case, have a look herehttp://www.aspfaq.com/etiquette.asp?id=5006 how to gather that information. -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Heute schon gebloggt?http://www.insidesql.de/blogs

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

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

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

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

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