get the max count per load# and only top 20

In using the following code:开发者_C百科

SELECT        SUM(LD_NUM) AS Expr1, LD_NUM
FROM            Bale
GROUP BY LD_NUM

returns Expr1 = 74987 and LD_NUM = 4411
returns Expr1 = 61768 and LD_NUM = 4412
returns Expr1 = 75021 and LD_NUM = 4413

etc..

if I 74987/4411 = 17, this gives me the count per LD_NUM

is there a way to return the relationship (17,4411), (14, 4412) , (17, 4413) and get or orderby 'Expr1' the top 20?

Hope this makes since.

SELECT TOP 20 SUM(LD_NUM) AS Expr1, LD_NUM, COUNT(LD_NUM) AS RecordCount
    FROM Bale
    GROUP BY LD_NUM
    ORDER BY Expr1 DESC

Not sure if you even need the SUM for any other purpose. It could be as simple as:

SELECT TOP 20 LD_NUM, COUNT(LD_NUM) AS RecordCount
    FROM Bale
    GROUP BY LD_NUM
    ORDER BY RecordCount DESC

you don't need to do any calculations to get the count,

count(LD_NUM)

is all that's needed

Try this

SELECT TOP 20 Expr1/LD_NUM,LD_NUM
FROM
(
   SELECT        SUM(LD_NUM) AS Expr1, LD_NUM
     FROM            Bale
    GROUP BY LD_NUM
) xx
ORDER BY xx.expr1