Special order by summary of groups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Special order by summary of groups

Author

Topic

tomino79
Starting Member

13 Posts

Posted - 2005-01-18 : 05:05:17

Hi,
please can anybody help me with create a select what will sort records by a summary of group ?
Example> I have Orders, Customer and Town

SELECT town.name, customer.name, sum(order.price)
FROM order LEFT JOIN customer on (order.c = customer.id)
LEFT JOIN town on (customer.t = town.id)

GROUP BY town.id, customer.id
ORDER BY town.name, customer.name

But I need to order the town by the sum of price
SELECT SUM(price) from order
...
GROUP BY Town.id.
So the first will be the town with the maximum total price and the last town will be the town what has a minimum totoal price, and in the groups i need to order customers by name.

Please if you know how to solve this give me a advice,

Thanks very much.
Tomas.
Slovakia.

RM
Yak Posting Veteran

65 Posts

Posted - 2005-01-18 : 05:22:04

SELECT town.name, customer.name, sum(order.price)
FROM order LEFT JOIN customer on (order.c = customer.id)
LEFT JOIN town on (customer.t = town.id)
GROUP BY town.id, customer.id
ORDER BY 3,2,1

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-18 : 05:30:07

ORDER BY 3 DESC,2,1

just for my post count :|

tomino79
Starting Member

13 Posts

Posted - 2005-01-18 : 05:46:30

Thanks for help, but probably I didnt explain it clearly:

The result what i get is
NY Frank 1500
NY Bob 1300
WA John 1000
WA Jack 1000
WA Lisa 1000
WA Mary 1000

But it is wrong, i need to order it not by the sum of the group town, customer but by the total sum NY has 2500$ and WA has 4000$ so the result order should be

WA
WA
WA
WA
and then
NY
NY...

because WA has the greatest market share.

Thanks

RM
Yak Posting Veteran

65 Posts

Posted - 2005-01-18 : 07:18:41

Probably somewhat like this ... (I haven't tested this)

SELECT town.name, customer.name, sum(order.price), Max(OrderPrice)
FROM order LEFT JOIN customer on (order.c = customer.id)
LEFT JOIN town on (customer.t = town.id)
LEFT JOIN (SELECT town.id, sum(order.price) OrderPrice
FROM order LEFT JOIN customer on (order.c = customer.id)
LEFT JOIN town on (customer.t = town.id)
GROUP BY town.name) towntotal
on towntotal.id = town.id
GROUP BY town.name, customer.name
Order By 4 Desc, 2, 1

tomino79
Starting Member

13 Posts

Posted - 2005-01-18 : 09:36:16

quote:
Originally posted by RM

Probably somewhat like this ... (I haven't tested this)

SELECT town.name, customer.name, sum(order.price), Max(OrderPrice)
FROM order LEFT JOIN customer on (order.c = customer.id)
LEFT JOIN town on (customer.t = town.id)
LEFT JOIN (SELECT town.id, sum(order.price) OrderPrice
FROM order LEFT JOIN customer on (order.c = customer.id)
LEFT JOIN town on (customer.t = town.id)
GROUP BY town.name) towntotal
on towntotal.id = town.id
GROUP BY town.name, customer.name
Order By 4 Desc, 2, 1

Thanks RM!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources