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

]]>