SQL Query help

Question

Post reply

SQL Query help

vsjayashri

Right there with Babe

Points: 797
More actions
June 19, 2008 at 4:51 am

#189457

hi
create table cust_product
(cust varchar(10), prod varchar(10))
insert into cust_product VALUES ('A','P')
insert into cust_product VALUES ('A','Q')
insert into cust_product VALUES ('A','P')
insert into cust_product VALUES ('B','Q')
insert into cust_product VALUES ('C','Q')
insert into cust_product VALUES ('A','P')
insert into cust_product VALUES ('B','P')
insert into cust_product VALUES ('C','P')
I need the output to be
cust No. of Product (P) No. of Product(Q)
A 3 1
B 1 1
C 1 1
Can anyone help?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

select cust,

count(case when prod='P' then prod end) as 'No. of Product (P)',

count(case when prod='Q' then prod end) as 'No. of Product (Q)'

from cust_product

group by cust

order by cust

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

vsjayashri Right there with Babe Points: 797 More actions · Answer 2

vsjayashri

Right there with Babe

Points: 797

June 19, 2008 at 5:16 am

#830988

thank u so much

dragos_sv Default port Points: 1478 More actions · Answer 3

or

select cust, sum(case prod when 'P' then 1 else 0 end) as 'No. of Product (P)' , sum(case prod when 'Q' then 1 else 0 end) as 'No. of Product (Q)'

from cust_product

group by cust

order by cust

vsjayashri Right there with Babe Points: 797 More actions · Answer 4

vsjayashri

Right there with Babe

Points: 797

June 19, 2008 at 5:33 am

#830999

thanks again... 🙂

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 5

Since you are using SQL Server 2005 you can use a PIVOT.

declare @cust_product table

(cust varchar(10), prod varchar(10))

insert into @cust_product VALUES ('A','P')

insert into @cust_product VALUES ('A','Q')

insert into @cust_product VALUES ('A','P')

insert into @cust_product VALUES ('B','Q')

insert into @cust_product VALUES ('C','Q')

insert into @cust_product VALUES ('A','P')

insert into @cust_product VALUES ('B','P')

insert into @cust_product VALUES ('C','P')

SELECT cust, [P],

FROM @cust_product

PIVOT(COUNT(prod) FOR prod IN ([P],

)) AS ProductPivot

sgmunson SSC Guru Points: 110640 More actions · Answer 6

How about using PIVOT?:

DECLARE @cust_product TABLE (cust varchar(10), prod varchar(10))

insert into @cust_product VALUES ('A','P')

insert into @cust_product VALUES ('A','Q')

insert into @cust_product VALUES ('A','P')

insert into @cust_product VALUES ('B','Q')

insert into @cust_product VALUES ('C','Q')

insert into @cust_product VALUES ('A','P')

insert into @cust_product VALUES ('B','P')

insert into @cust_product VALUES ('C','P')

SELECT cust, P AS COUNT_OF_P, Q AS COUNT_OF_Q

FROM @cust_product

PIVOT (COUNT(prod) FOR prod IN ([P],

)) AS UNPVT

Enjoy!

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)