|
Hi all, I've done a query that gives me the highest sales value for each category for each country. But I only want to know what is the category that sold the highest for each countr. This is my query that gives me those values, but without the last filter for the highest category for each country, and since I can't do a MAX (SUM(od.unitprice * od.Quntity) I was wondering if there is a Subquery I could use Select country, categoryname, SUM(od.unitprice * od.Quantity) as Valor
From Categories c
Join Products p on c.CategoryID = p.CategoryID
Join [Order Details] od on p.ProductID = od.ProductID
Join Orders o on od.orderID = o.OrderID
Join Customers c2 on o.CustomerID = c2.CustomerID
Group by Country, CategoryName, year(o.OrderDate)
Having year(o.OrderDate) = 1997
|
|
Hi, thank you all for the answers, I finally did it doing a partition, the result is here: Select * from (Select ROW_NUMBER() OVER(PARTITION BY COUNTRY ORDER BY SUM(od.unitprice * od.Quantity) DESC) AS 'ROW_NUMBER', country, categoryname, SUM(od.unitprice * od.Quantity) as Valor From Categories c Join Products p on c.CategoryID = p.CategoryID Join [Order Details] od on p.ProductID = od.ProductID Join Orders o on od.orderID = o.OrderID Join Customers c2 on o.CustomerID = c2.CustomerID Group by Country, CategoryName, year(o.OrderDate) Having year(o.OrderDate) = 1997) as tbl Where tbl.ROW_NUMBER = 1 |
It seems to me that "OUTER APPLY (select top 1 ...)" might be useful here. The sample query is ambiguous on which tables have the 'country' and 'categoryname' columns, so I didn't try to make an example.
I do not really understand the question, and without the schema I can also not make sample code, and a few very general hints don't seem worthwhile.
Some test data and the expected result set are helpful to get an answer.