Top 5 based on total order query

Question

Post reply

Top 5 based on total order query

b_boy

SSCertifiable

Points: 5145
More actions
August 6, 2008 at 10:42 am

#190723

Hello
I am using the following query;
SELECT ShipCountry,SUM (OrderTotal)as [Total Order], SUM (OrderTotalExVat) as [Total Order Excl.VAT],
AVG(OrderTotal) as [AVG Order value - Inc.VAT], AVG(OrderTotalExVat) as [AVG Order Value - Ex.VAT]
FROMT_OrderHeader
WHEREOrderDate BETWEEN GETDATE()-365 AND GETDATE()
GROUP BYShipCountry
ORDER BYShipCountry ASC
And i want to see the top 5 countries based on Total Orders made(Sales), can anyone help

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Just a guess, but is this what you are looking for?

SELECT TOP (5)

ShipCountry,

SUM (OrderTotal)as [Total Order],

SUM (OrderTotalExVat) as [Total Order Excl.VAT],

AVG(OrderTotal) as [AVG Order value - Inc.VAT],

AVG(OrderTotalExVat) as [AVG Order Value - Ex.VAT]

FROM

T_OrderHeader

WHERE

OrderDate BETWEEN GETDATE()-365 AND GETDATE()

GROUP BY

ShipCountry

ORDER BY

SUM (OrderTotal) DESC,

ShipCountry ASC

😎

b_boy SSCertifiable Points: 5145 More actions · Answer 2

Arghh, I see where I went wrong now!!!

Thanks for the guide

b_boy SSCertifiable Points: 5145 More actions · Answer 3

Hello Lyn

Is there a way i can group the remaining countries as 'Others', so my end results will be the top and 5 and other countries?

Abhijit More SSCertifiable Points: 6897 More actions · Answer 4

I think you have create the union for "Other" countries

Abhijit - http://abhijitmore.wordpress.com

b_boy SSCertifiable Points: 5145 More actions · Answer 5

But how will it returns the list of countries not in the top 5 list?

Melville SSCommitted Points: 1985 More actions · Answer 6

That is an interesting query, I guess that you want:

Number1 Actual value

Number2 Actual value

Number3 Actual value

Number4 Actual value

Number5 Actual value

Others Total of values outside top 5

I'll do an example, give me a few minutes.

b_boy SSCertifiable Points: 5145 More actions · Answer 7

b_boy

SSCertifiable

Points: 5145

August 7, 2008 at 9:05 am

#854521

Thats right!!!

Melville SSCommitted Points: 1985 More actions · Answer 8

The only difficulty with the union is that if the others total is more than, say, the fifth highest then it will appear above it unless you use a contrivance (see below).

declare @T_OrderHeader table (ShipCountry varchar(50), OrderTotal money)

insert into @T_OrderHeader (ShipCountry, OrderTotal) values ('Greece', 100.00)

insert into @T_OrderHeader (ShipCountry, OrderTotal) values ('Spain', 5.00)

insert into @T_OrderHeader (ShipCountry, OrderTotal) values ('England', 120.00)

insert into @T_OrderHeader (ShipCountry, OrderTotal) values ('Italy', 44.00)

insert into @T_OrderHeader (ShipCountry, OrderTotal) values ('Germany', 16.09)

insert into @T_OrderHeader (ShipCountry, OrderTotal) values ('France', 999.99)

insert into @T_OrderHeader (ShipCountry, OrderTotal) values ('Portugal', 10.00)

insert into @T_OrderHeader (ShipCountry, OrderTotal) values ('Turkey', 7.64)

select top 5

ShipCountry

, sum(OrderTotal) as [Total Order]

, 1 as contrivance

from

@T_OrderHeader

group by

ShipCountry

union

select

'Others' as ShipCountry

, sum(OrderTotal) as [Total Order]

, 99 as contrivance

from

@T_OrderHeader

where

ShipCountry not in (select top 5 ShipCountry from @T_OrderHeader group by ShipCountry order by sum(OrderTotal) desc)

order by

3, sum(OrderTotal) desc

This yields the following, which I think is correct:

ShipCountry Total Order contrivance

-------------------------------------------------- --------------------- -----------

France 999.99 1

England 120.00 1

Greece 100.00 1

Italy 44.00 1

Germany 16.09 1

Others 22.64 99

(6 row(s) affected)

Melville SSCommitted Points: 1985 More actions · Answer 9

There are alternatives:

Build a temp table with the 5 values, then insert the 'others' row and you have a table with 6 rows that you can order and report on as you like.

declare a variable that you set to the min value of the top 5, and use that to generate the 'Others' total.

Let me know if that works or doesn't!

Melville SSCommitted Points: 1985 More actions · Answer 10

Oh and I've not done anything to help if the 5th and 6th values are tied.

If you have a tie for fifth:

insert into @T_OrderHeader (ShipCountry, OrderTotal) values ('Russia', 16.09)

Sql 2005 will still only show 5 (in a top 5 query) and 'choose' one of the tie to show:

ShipCountry Total Order contrivance

-------------------------------------------------- --------------------- -----------

France 999.99 1

England 120.00 1

Greece 100.00 1

Italy 44.00 1

Germany 16.09 1

Others 38.73 99

(6 row(s) affected)