Multiple names for the one thing,only want to count them once!

[damien]

Hi,

I have a product which has a number of referrence no.s ranging from 1 number to 4.But I only want to count it once using its first referrence no.The reason is, counting 4 records of the once item would skew my data for graphs and the like.

any help would be much apreciated.

Damien

 

[tlbroadbent]

You'll need to use aggregate functions GROUP BY and MIN.

Examples:

Select ProductID, MIN(RefNo) As RefNum
From ProductTbl
Group By ProductID

Select a.ProductID, a.RefNo, a.Description, a.Qty
From ProductTbl a Inner Join
(Select ProductID, MIN(RefNo) As RefNum
From ProductTbl Group By ProductID) As b
On a.ProductID=b.ProductID And a.RefNo=b.RefNum Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker