Need to Order By SUM'd value

[999Dom999]

Here is my statement

SELECT SNAME.SN_TERRTRY, SLOOK.LK_DESC, SUM(SANAL.SA_QTY),SUM(SANAL.SA_COST),SUM(SANAL.SA_TRVALUE) FROM SANAL INNER JOIN SNAME ON SANAL.SA_ACCOUNT = SNAME.SN_ACCOUNT JOIN SLOOK ON SLOOK.LK_CODE = SNAME.SN_TERRTRY GROUP BY SNAME.SN_TERRTRY

ORDER BY ????

I want the sort the order by values given to me from SUM(SANAL.SA_TRVALUE) I tried putting it in without the SUM but doesn't work. Doesn't work without too. Any ideas?

 

[bborissov]

SELECT * FROM (
SELECT SNAME.SN_TERRTRY,
       SLOOK.LK_DESC, 
       SUM(SANAL.SA_QTY)     AS SA_QTY,
       SUM(SANAL.SA_COST)    AS SA_COST,
       SUM(SANAL.SA_TRVALUE) AS SA_TRVALUE
FROM SANAL
INNER JOIN SNAME ON SANAL.SA_ACCOUNT = SNAME.SN_ACCOUNT
INNER JOIN SLOOK ON SLOOK.LK_CODE = SNAME.SN_TERRTRY
GROUP BY SNAME.SN_TERRTRY )Tst
ORDER BY  SA_TRVALUE

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[djj55]

What about the number of the calculated column. More difficult to read but may work.

SELECT SNAME.SN_TERRTRY, SLOOK.LK_DESC, SUM(SANAL.SA_QTY),SUM(SANAL.SA_COST),SUM(SANAL.SA_TRVALUE) 
FROM SANAL 
INNER JOIN SNAME ON SANAL.SA_ACCOUNT = SNAME.SN_ACCOUNT 
JOIN SLOOK ON SLOOK.LK_CODE = SNAME.SN_TERRTRY 
GROUP BY SNAME.SN_TERRTRY
ORDER BY 3

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[bborissov]

As markros said to me you could use this syntax also:

SELECT SNAME.SN_TERRTRY,
       SLOOK.LK_DESC,
       SUM(SANAL.SA_QTY)     AS SA_QTY,
       SUM(SANAL.SA_COST)    AS SA_COST,
       SUM(SANAL.SA_TRVALUE) AS SA_TRVALUE
FROM SANAL
INNER JOIN SNAME ON SANAL.SA_ACCOUNT = SNAME.SN_ACCOUNT
INNER JOIN SLOOK ON SLOOK.LK_CODE = SNAME.SN_TERRTRY
GROUP BY SNAME.SN_TERRTRY
ORDER BY SUM(SANAL.SA_TRVALUE)

But I still prefer derived table. For me it is more visible which column you use for ORDERing.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[999Dom999]

Thanks for all your replies.

First suggestion I couldn't get to work.

Using the order by column no. worked a treat.

The last one didn't work for me I had previously tried it but I'm using ODBC SQL in php to connect to foxpro db not sure if it fully supports everything.

The final way was that works the best taken from the first suggestion is changing my line:

SUM(SANAL.SA_TRVALUE) AS SA_TRVALUE

Then

Order by SA_TRVALUE

Thanks for your help

 

[bborissov]

Why ask FoxPro questions here?

All ways works in SQL server.
And none of them will work in VFP with ODBC connection but djj5 suggestion.
For all others you must have VFP9 and OLEDB conection.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.