CODEWITHSUNDEEP
sql-serversql-order-by
niha
niha

Reputation: 37

how to add the order by Sum function in a query?

i just have this query and I want to add the ordering of its records by the result of the sum function, how to add this? 

 Select bus.trans_comp_id, 
 SUM(bus.passengers*trips.cost) 
 From bus inner join trips on bus.ID=trips.bus_id 
 group by bus.trans_comp_id

out put of this: 

trans_comp_id  
 1)   1:412000.00
 2)   2:75000.00

I want it with desc order to have the out put:

trans_comp_id  
 2)   2:75000.00
 1)   1:412000.00

Upvotes: 1

Views: 69

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Simply add an ORDER BY clause that makes use of an alias of the computed value defined in the SELECT clause:

 SELECT bus.trans_comp_id, 
        SUM(bus.passengers*trips.cost) AS s
 FROM bus 
 INNER JOIN trips ON bus.ID=trips.bus_id 
 GROUP BY bus.trans_comp_id
 ORDER BY s DESC

Upvotes: 1

Gabor Rajczi
Gabor Rajczi

Reputation: 471

You can simply add the SUM function to the ORDER BY caluse:

Select bus.trans_comp_id, 
 SUM(bus.passengers*trips.cost) 
 From bus inner join trips on bus.ID=trips.bus_id 
 group by bus.trans_comp_id 
 order by SUM(bus.passengers*trips.cost) desc

Upvotes: 1

Related Questions