Distinct - 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)

Distinct

Author

Topic

SGB19
Starting Member

10 Posts

Posted - 2007-03-02 : 13:34:30

I want this output

Supplier Number Name Items supplied
806 Keller 4
805 Albany 4
803 3V 7

(3 row(s) affected)

But I am getting this output
Supplier Number Name Items supplied
806 Keller 4
805 Albany 4
803 3V 8
(3 row(s) affected

from this query
select distinct s.Suppliernbr, s.name, count(po_.fk_partnbr)
from part p join po_line_item po_
on po_.fk_partnbr = p.partnbr
join purchase_order po
on po_.fk_ponbr = po.ponbr
join supplier s
on po.fk_suppliernbr = s.suppliernbr
where s.state = 'or'
group by po_.fk_ponbr, s.name, s.suppliernbr
order by s.suppliernbr desc

It looks like, the result is showing the purchase order count instead of the parts supplied by each supplier on the purchase orders. I need to find the parts purchased not the purchase orders count.
The tables are SUPPLIER, PURCHASE_ORDER, PO_LINE_ITEM, PART

Any comment is welcome.

Thank you

sgb19

********
***sg***
********

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-02 : 14:18:30

When you use distinct in the select list you are asking for all distinct rows and given that you are grouping, all rows are already distinct. What you want is a distinct count, like this

select s.Suppliernbr, s.name, count(distinct po_.fk_partnbr)
from part p join po_line_item po_
on po_.fk_partnbr = p.partnbr 
join purchase_order po
on po_.fk_ponbr = po.ponbr
join supplier s
on po.fk_suppliernbr = s.suppliernbr
where s.state = 'or' 
group by po_.fk_ponbr, s.name, s.suppliernbr
order by s.suppliernbr desc

SGB19
Starting Member

10 Posts

Posted - 2007-03-02 : 15:33:09

Hi

I added the distinct count for the part as you suggested it and have the expected result. select distinct s.Suppliernbr, s.name, count(distinct po_.fk_partnbr).

Thank you for the hint.

sgb19

********
***sg***
********

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-02 : 17:50:18

Given that you are grouping already, you don't need the first distinct, remove it.

select s.Suppliernbr, s.name, count(distinct po_.fk_partnbr)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources