SQL sort order.

[toon10]

(I initially had this on the SQL forumns but they are telling me I need to post here as it's SQL through VB6)

I'm running the following query through the visual basic 6 data designer against a SQL 2005 database.

SELECT `Part No`, SUM(Val - `Cost Of Sale`) AS Margin, Description,
`Product Group`
FROM tblSales
WHERE (`Invoice Date` BETWEEN (# 01 / 01 / 2006 #) AND
(# 01 / 02 / 2007 #))
GROUP BY `Part No`, Description, `Product Group`
ORDER BY SUM(Val - `Cost Of Sale`) DESC

This works OK apart from the sort order. It lists margins of "0" first, then "- numbers" and then the order is correct, i.e. largest positive number followed by the next largest and so on.

i.e. 0, 0, 0, -14, 590, 570, 300, 200, etc.

I want to be able to display so that largest positive number followed by the next largest number are at the top, eventually going to 0 and then eventuially listing the - numbers.

i.e. 590, 570, 300, 200, 0, 0, 0, -14.

I'm presuming this isn't happening now because maybe the margin field isn't numeric?

I've tried to use a CONVERT statement but get errors. Is there another way to convert the calculated margin field to numeric so I can order the column descending?

Thanks

 

[djj55]

Hello,
Have you tried putting Margin as the order by instead of the equation?

SELECT `Part No`, SUM(Val - `Cost Of Sale`) AS Margin, Description, 
    `Product Group`
FROM tblSales
WHERE (`Invoice Date` BETWEEN (# 01 / 01 / 2006 #) AND 
    (# 01 / 02 / 2007 #))
GROUP BY `Part No`, Description, `Product Group`
ORDER BY [b]Margin[/b] DESC

djj

 

[toon10]

Hi thanks for replying,

yes I tried that but it returns an error "No Value given for one or more required parameters" when I do that.

 

[PeterWallace]

are both Val and 'Cost of Sale' the same type of field .e.g.
currency , double ?

if not try
SUM(cdbl(Val) - cdbl(`Cost Of Sale`))

 

[toon10]

Hi

Yes that works a treat. thanks for the help. I knew it was something to do with the field types but didn't know how to convert them.

Thanks again
Andrew