Using Northwind Traders sample database in SQL Server, I want to do the following:
I want the top selling category in each country:
I did the following in SQL Server 2000:
insert into #OrderAmtbyCategoryInEachCountry
(OrderAmt, CategoryName, ShipCountry)
(Select Round(sum([order details].UnitPrice*Quantity)-Discount),00) As OrderAmt, CategoryName, ShipCountry
from [order details], orders,categories, products
where [order details].orderid= orders.orderid
and products.productid=[order details].productid
and categories.categoryid = products.categoryid
group by CategoryName, shipcountry)
select max(OrderAmt), shipcountry, categoryname
group by shipcountry, categoryname
These two SQL queries are not giving me the top category by order amt
in each country, rather they are giving me the max(order amt) for each unique combination of category and shipcountry.
Could someone tell me how to get the top category in each country?