how to pull the count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to pull the count

Author

Topic

treeskin
Starting Member

22 Posts

Posted - 2010-02-08 : 21:31:27

Hello Experts,

Need your help on below.

1) How to write a query to pull for product from the same supplier for which the product code could have different from each other? See below example result:

Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B

2) How to write a query to pull a product list for which the different product having the same product code? See below example result:
Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B

Thank you :-)

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-08 : 23:34:07

Iam assuming you are trying to find out the Duplicate Product code.
select Pcode,product,description,suplier from
(
select rownum=Row_Number()over(partition by [produt code] order by [produt code]),[Product code]Pcode,product,description,suplier from tbl
)t
where t.rownum > 1

treeskin
Starting Member

22 Posts

Posted - 2010-02-09 : 20:12:45

Hi haroon2k9,

Thanks. Your query just work partially because it returns the non-duplicate as well.

Any guide for #1?

Thanks again.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-10 : 00:46:53

1.
Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY PRODUCT,SUPPLIER ORDER BY PRODUCTCODE),[PRODUT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum=1 ORDER BY PRODUCTCODE

2.

Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY [PRODUCT CODE],PRODUCT ORDER BY PRODUCTCODE),[PRODUCT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum > 1 ORDER BY PRODUCTCODE

Could pls check and let me know

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 01:21:48

quote:
Originally posted by haroon2k9

1.
Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY PRODUCT,SUPPLIER ORDER BY PRODUCTCODE),[PRODUT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum=1 ORDER BY PRODUCTCODE

2.

Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY [PRODUCT CODE],PRODUCT ORDER BY PRODUCTCODE),[PRODUCT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum > 1 ORDER BY PRODUCTCODE

Could pls check and let me know

------------------------------------------------------------------------------------------------------
SQL Server MVP

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-10 : 01:38:13

Hi visakh16,

i dint get anything by this ..

quote:
Originally posted by visakh16

quote:
Originally posted by haroon2k9

1.
Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY PRODUCT,SUPPLIER ORDER BY PRODUCTCODE),[PRODUT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum=1 ORDER BY PRODUCTCODE

2.

Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY [PRODUCT CODE],PRODUCT ORDER BY PRODUCTCODE),[PRODUCT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum > 1 ORDER BY PRODUCTCODE

Could pls check and let me know

------------------------------------------------------------------------------------------------------
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 01:43:01

quote:
Originally posted by haroon2k9

Hi visakh16,

i dint get anything by this ..

quote:
Originally posted by visakh16

quote:
Originally posted by haroon2k9

1.
Product Code Product Description Supplier
1234 Pencil Supplier A
3456 Pencil Supplier A
5678 Rubber Supplier B
9870 Rubber Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
FROM
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY PRODUCT,SUPPLIER ORDER BY PRODUCTCODE),[PRODUT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum=1 ORDER BY PRODUCTCODE

2.

Product Code Product Description Supplier
1234 Pencil Supplier A
1234 Rubber Supplier B
7890 A4 Paper Supplier A
7890 Ruler - 12cm Supplier B

SELECT [PRODUCT COE],PRODUCT,DESCRIPTION,SUPPLIER
FROM
(
SELECT rownum=ROW_NUMBER()OVER(PARTITION BY [PRODUCT CODE],PRODUCT ORDER BY PRODUCTCODE),[PRODUCT CODE],PRODUCT,DESCRIPTION,SUPPLIER FROM TBL)
)T WHERE T.rownum > 1 ORDER BY PRODUCTCODE

Could pls check and let me know

------------------------------------------------------------------------------------------------------
SQL Server MVP

you had some unwanted brackets so i tidied it up.
Just noticed you've left out FROM also
Could you please test your solutions before posting?

------------------------------------------------------------------------------------------------------
SQL Server MVP

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-10 : 01:52:40

Oh!.iam extremy sorry for that.

quote:

you had some unwanted brackets so i tidied it up.
Just noticed you've left out FROM also

sure.

quote:

Could you please test your solutions before posting?

Thank you very much visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 01:54:33

No problem
You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-10 : 02:01:24

I would like to thank you visakh16.you are also one of my sql guru here.actually the use of partition i learnt from u and here(sqlteam forum)..iam also just a beginner of sql.i started learning it here only.
i would like to thanks for everyone(u,kristen,madhivanan..etc) who is contributing answers are very excellent..because of this iam a beginner of this sql,started contributing my answers here.so all my gredits goes here..

Thanks again for everyone contributing this forum..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 02:32:53

quote:
Originally posted by haroon2k9

I would like to thank you visakh16.you are also one of my sql guru here.actually the use of partition i learnt from u and here(sqlteam forum)..iam also just a beginner of sql.i started learning it here only.
i would like to thanks for everyone(u,kristen,madhivanan..etc) who is contributing answers are very excellent..because of this iam a beginner of this sql,started contributing my answers here.so all my gredits goes here..

Thanks again for everyone contributing this forum..

You're welcome

Glad that I could help out so much in your learnings
Follow this forum closely and you will get opportunity to learn a lot, thanks to all the great gurus who participate here

------------------------------------------------------------------------------------------------------
SQL Server MVP

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-10 : 03:21:04

quote:

You're welcome
Glad that I could help out so much in your learnings
Follow this forum closely and you will get opportunity to learn a lot, thanks to all the great gurus who participate here

Definitely!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources