order query on set function

[aas4mis]

I have a query that works fine when ordered by the puller id (cf.op.id). The problem comes when I try to order by the sum of the boxes (sum(cf.qq)).

I have read "UniVerse SQL Reference" and "UniVerse SQL User Guide". I don't see any way possible to display the results the way I want.

This is my working query.

SELECT                                   
SQL+CF.OP.ID, SUM(CF.QQ)                  
SQL+FROM UNNEST PICK.TRANS.HDC ON CF.OP.ID
SQL+WHERE CF.QQ > 0                       
SQL+GROUP BY CF.OP.ID;  
 

My Results.

Cf.op.id SUM ( CF.QQ )
AAM                  4
ABC                  1
ADB                 56
ADD                  2
ADH                  2
 

My Desired Results

Cf.op.id SUM ( CF.QQ )
ADB                  56
AAM                  4
ADD                  2
ADH                  2
ABC                  1
 

Does anybody know how to group by sum(cf.qq) and get the desired results?

[docdiesel]

Hi,

SELECT                                   
  CF.OP.ID,
  SUM(CF.QQ) as SUM_CF_QQ
FROM
  UNNEST PICK.TRANS.HDC ON CF.OP.ID
WHERE
  CF.QQ > 0                       
GROUP BY
  CF.OP.ID
ORDER BY
  SUM(CF.QQ) DESC
;

should do the job.

Regards,

Bernd

[aas4mis]

Hi,

SELECT                                   
  CF.OP.ID,
  SUM(CF.QQ) as SUM_CF_QQ
FROM
  UNNEST PICK.TRANS.HDC ON CF.OP.ID
WHERE
  CF.QQ > 0                       
GROUP BY
  CF.OP.ID
ORDER BY
  SUM(CF.QQ) DESC
;

should do the job.

Regards,

Bernd

Thanks, I literally spent an entire work day on this query researching what I was doing wrong. I should've came here a lot sooner, I always find an answer here. I did have to change the order by clause, it was throwing a syntax error "unexpected verb SUM". The following code worked perfectly.

>SELECT                                                        
SQL+MIN(CF.DT) AS "FIRST CF", CF.OP.ID, SUM(CF.QQ) AS SUM_CF_QQ
SQL+FROM UNNEST PICK.TRANS.HDC ON CF.OP.ID                     
SQL+WHERE CF.QQ > 0                                            
SQL+GROUP BY CF.OP.ID                                          
SQL+ORDER BY SUM_CF_QQ DESC;                                   
 

Thanks for all your help!