views:

130

answers:

3

If we start with the following simple SQL statement which works.

SELECT sor.FPARTNO, sum(sor.FUNETPRICE)
FROM sorels sor
GROUP BY sor.FPARTNO

FPartNo is the part number and the Funetprice is obviously the net price. The user also wants the description and this causes a problem. If I follow up with this:

SELECT sor.FPARTNO, sor.fdesc, sum(sor.FUNETPRICE)
FROM sorels sor
GROUP BY sor.FPARTNO, sor.fdesc

If there are multiple variations of the description for that part number, typically very small variations in the text, then I don't actually aggregate on the part number. Make sense?

I'm sure this must be simple. How can I return the first fdesc that corresponds to the part number? Any of the description variations would suffice as they are almost entirely identical.

Edit: The description is a text field.

A: 

Have you tried

SELECT sor.FPARTNO, MIN(sor.fdesc), sum(sor.FUNETPRICE) 
FROM sorels sor 
GROUP BY sor.FPARTNO

Or even MAX can do.

Try casting the NTEXT field

DECLARE @sorels TABLE(
        FPARTNO INT,
        fdesc NTEXT,
        FUNETPRICE FLOAT
)

SELECT sor.FPARTNO, MIN(CAST(sor.fdesc AS VARCHAR(4000))), sum(sor.FUNETPRICE)  
FROM @sorels sor  
GROUP BY sor.FPARTNO
astander
I tried Max, and just now tried Min, and received the following error:Error SQL Server Database Error: The text, ntext, and image data types are invalid in this subquery or aggregate expression.
DavidStein
What is the type of **sor.fdesc** ?
astander
sor.fdesc is a text field as I said above in my previous edit. :)
DavidStein
A: 

Have not had a chance to test this, but it should be close. If you were using SQL Server 2005, it would be a lot cleaner with CTEs.

SELECT agg.FPARTNO,
       (SELECT TOP 1 inner.FDESC FROM sorels inner WHERE inner.FPARTNO = agg.FPARTNO) FDESC,
       agg.FUNETPRICESUM
FROM   (SELECT sor.FPARTNO,
               sum(sor.FUNETPRICE) FUNETPRICESUM
        FROM sorels sor 
        GROUP BY sor.FPARTNO) agg
Bryan Batchelder
Inner is a reserved word, I believe.
DavidStein
+2  A: 

If you can't upgrade to SQL Server 2005 for a (max) type :-)

Try this. SUBSTRING works on blobs, but returns varchar for text. So the aggregate should work and you lose some data

SELECT
   sor.FPARTNO, MIN(SUBSTRING(sor.fdesc, 1, 8000)), sum(sor.FUNETPRICE)
FROM
    sorels sor
GROUP BY
    sor.FPARTNO, SUBSTRING(sor.fdesc, 1, 8000)
gbn
Don't you still have to use MIN or MAX, otherwise you will get spurious groups?
recursive
oops! fixed it now
gbn