Sorting on Sum of Tables 2

[ksbrace]

Hello,
I am trying to sort based on the sum of a few tables, but it keeps prompting me for a value for Total. Any help in achieving this would be great! Thanks in advance:

SELECT DISTINCTROW Customers.CustomerID, Customers.FirstName, Customers.LastName, Jobs.JobID, Jobs.Days, Jobs.SpringRaking, Jobs.LawnCutting, Jobs.FallLeaves, Jobs.SnowPlowing, Jobs.Labor, Jobs.Materials, Jobs.Misc, Sum(Jobs.SpringRaking + Jobs.LawnCutting + Jobs.FallLeaves + Jobs.SnowPlowing + Jobs.Labor + Jobs.Materials + Jobs.Misc) as Total FROM Customers  INNER JOIN Jobs ON Customers.CustomerID =jobs.CustomerID GROUP BY Customers.CustomerID, Jobs.jobid, Customers.FirstName, Customers.LastName, Jobs.Days,Jobs.SpringRaking, Jobs.LawnCutting, Jobs.FallLeaves, Jobs.SnowPlowing, Jobs.Labor, Jobs.Materials, Jobs.Misc order by total asc;

 

[lynchg]

I don't think that the query can recognize the 'Total' alias as a field to group by within the same query.

You may need to first create one query to get your total and then pull that query into your 2nd query to do the grouping.

 

[SkipVought]

Hi,

How about...

.....
 Order By Sum(Jobs.SpringRaking + Jobs.LawnCutting + Jobs.FallLeaves + Jobs.SnowPlowing + Jobs.Labor + Jobs.Materials + Jobs.Misc)

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[PHV]

Another way:
ORDER BY 13 ASC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ksbrace]

PHV,
Would you mind elaborating a bit on your solution. What does the 13 mean? Thanks,

 

[PHV]

Sort by the column having the 13th ordinal position in the SELECT list.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886