SQL Server Performance Forum – Threads Archive
How to count Rows with datatype varchar
I have the following scenarioIn 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
]]>