|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
i need one query1)ID (primary key) 2)product 3) value (can be either p or q) I need to write the querry which gives the result as Product, count when value=p for this product, count when value=q for this product E.g table is : 1 A p 2 A p 3 B p 4 B q 5 B q 6 c p Then the querry shud result the following: A 2 0 B 1 2 C 1 0 Try using CASE:
SELECT product, SUM (CASE value WHEN 'p' THEN 1 ELSE 0 END) AS PCount, SUM (CASE value WHEN 'q' THEN 1 ELSE 0 END) AS QCount GROUP BY product ORDER BY product -- Show quoteHope this helps. Dan Guzman SQL Server MVP "saurabh" <saurabh***@gmail.com> wrote in message news:1154002002.882823.201330@s13g2000cwa.googlegroups.com... > Hey i have a tabel which has 3 coloumns > 1)ID (primary key) > 2)product > 3) value (can be either p or q) > > I need to write the querry which gives the result as > Product, count when value=p for this product, count when value=q for > this product > > > E.g > table is : > 1 A p > 2 A p > 3 B p > 4 B q > 5 B q > 6 c p > > Then the querry shud result the following: > > A 2 0 > B 1 2 > C 1 0 > SELECT Product,
SUM(case when value = 'p' then 1 else 0 end) as PCount, SUM(case when value = 'q' then 1 else 0 end) as QCount FROM Whatever GROUP BY Product Roy Harvey Beacon Falls, CT Show quote On 27 Jul 2006 05:06:42 -0700, "saurabh" <saurabh***@gmail.com> wrote: >Hey i have a tabel which has 3 coloumns >1)ID (primary key) >2)product >3) value (can be either p or q) > >I need to write the querry which gives the result as >Product, count when value=p for this product, count when value=q for >this product > > >E.g >table is : >1 A p >2 A p >3 B p >4 B q >5 B q >6 c p > >Then the querry shud result the following: > >A 2 0 >B 1 2 >C 1 0 thnk you very much guys...it worked
Roy Harvey wrote: Show quote > SELECT Product, > SUM(case when value = 'p' then 1 else 0 end) as PCount, > SUM(case when value = 'q' then 1 else 0 end) as QCount > FROM Whatever > GROUP BY Product > > Roy Harvey > Beacon Falls, CT > > On 27 Jul 2006 05:06:42 -0700, "saurabh" <saurabh***@gmail.com> wrote: > > >Hey i have a tabel which has 3 coloumns > >1)ID (primary key) > >2)product > >3) value (can be either p or q) > > > >I need to write the querry which gives the result as > >Product, count when value=p for this product, count when value=q for > >this product > > > > > >E.g > >table is : > >1 A p > >2 A p > >3 B p > >4 B q > >5 B q > >6 c p > > > >Then the querry shud result the following: > > > >A 2 0 > >B 1 2 > >C 1 0 SELECT
ID, p = SUM(CASE WHEN [value] = 'p' THEN 1 ELSE 0 END), q = SUM(CASE WHEN [value] = 'q' THEN 1 ELSE 0 END) FROM table GROUP BY ID Show quote "saurabh" <saurabh***@gmail.com> wrote in message news:1154002002.882823.201330@s13g2000cwa.googlegroups.com... > Hey i have a tabel which has 3 coloumns > 1)ID (primary key) > 2)product > 3) value (can be either p or q) > > I need to write the querry which gives the result as > Product, count when value=p for this product, count when value=q for > this product > > > E.g > table is : > 1 A p > 2 A p > 3 B p > 4 B q > 5 B q > 6 c p > > Then the querry shud result the following: > > A 2 0 > B 1 2 > C 1 0 > Sorry, I misread... change ID to Product
Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:u2pCgbXsGHA.4140@TK2MSFTNGP06.phx.gbl... > SELECT > ID, > p = SUM(CASE WHEN [value] = 'p' THEN 1 ELSE 0 END), > q = SUM(CASE WHEN [value] = 'q' THEN 1 ELSE 0 END) > FROM table > GROUP BY ID Hi
create table #t (c1 int,c2 char(1),c3 char(1)) insert into #t values (1,'a','p') insert into #t values (2,'a','p') insert into #t values (3,'b','q') insert into #t values (4,'b','q') insert into #t values (5,'c','p') select c2,count(case when c3='p' then 1 end)as 'p', count(case when c3='q' then 1 end)as 'q' from #t group by c2 Show quote "saurabh" <saurabh***@gmail.com> wrote in message news:1154002002.882823.201330@s13g2000cwa.googlegroups.com... > Hey i have a tabel which has 3 coloumns > 1)ID (primary key) > 2)product > 3) value (can be either p or q) > > I need to write the querry which gives the result as > Product, count when value=p for this product, count when value=q for > this product > > > E.g > table is : > 1 A p > 2 A p > 3 B p > 4 B q > 5 B q > 6 c p > > Then the querry shud result the following: > > A 2 0 > B 1 2 > C 1 0 > Use PIVOT in SQL 2005!
create table #t (c1 int,c2 char(1),c3 char(1)) insert into #t values (1,'a','p') insert into #t values (2,'a','p') insert into #t values (3,'b','q') insert into #t values (4,'b','q') insert into #t values (5,'c','p') select c2,count(case when c3='p' then 1 end)as 'p', count(case when c3='q' then 1 end)as 'q' from #t group by c2 go SELECT * FROM #t PIVOT ( COUNT(c1) FOR c3 In ( [p], [q] ) ) AS x Thanks to Uri for the DDL ; ) Damien Show quote "Uri Dimant" wrote: > Hi > create table #t (c1 int,c2 char(1),c3 char(1)) > > insert into #t values (1,'a','p') > insert into #t values (2,'a','p') > insert into #t values (3,'b','q') > insert into #t values (4,'b','q') > insert into #t values (5,'c','p') > > select c2,count(case when c3='p' then 1 end)as 'p', > count(case when c3='q' then 1 end)as 'q' > from #t > group by c2 > > "saurabh" <saurabh***@gmail.com> wrote in message > news:1154002002.882823.201330@s13g2000cwa.googlegroups.com... > > Hey i have a tabel which has 3 coloumns > > 1)ID (primary key) > > 2)product > > 3) value (can be either p or q) > > > > I need to write the querry which gives the result as > > Product, count when value=p for this product, count when value=q for > > this product > > > > > > E.g > > table is : > > 1 A p > > 2 A p > > 3 B p > > 4 B q > > 5 B q > > 6 c p > > > > Then the querry shud result the following: > > > > A 2 0 > > B 1 2 > > C 1 0 > > > > > > Use PIVOT in SQL 2005! We don't know that the original poster has SQL 2005. |
|||||||||||||||||||||||
Other interesting topics