August 6, 2008 at 10:42 am
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]
FROM T_OrderHeader
WHERE OrderDate BETWEEN GETDATE()-365 AND GETDATE()
GROUP BY ShipCountry
ORDER BY ShipCountry ASC
And i want to see the top 5 countries based on Total Orders made(Sales), can anyone help
August 6, 2008 at 10:48 am
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
😎
August 7, 2008 at 2:17 am
Arghh, I see where I went wrong now!!!
Thanks for the guide
August 7, 2008 at 3:22 am
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?
August 7, 2008 at 3:51 am
I think you have create the union for "Other" countries
Abhijit - http://abhijitmore.wordpress.com
August 7, 2008 at 3:55 am
But how will it returns the list of countries not in the top 5 list?
August 7, 2008 at 8:53 am
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.
August 7, 2008 at 9:05 am
Thats right!!!
August 7, 2008 at 9:10 am
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)
August 7, 2008 at 9:13 am
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!
August 7, 2008 at 9:19 am
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)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply