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

asked 16 Dec '20, 15:55

bernie_chale's gravatar image

bernie_chale
415
accept rate: 0%

edited 17 Dec '20, 06:26

Justin%20Willey's gravatar image

Justin Willey
7.3k128165240

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.

(17 Dec '20, 08:16) Bud Durland MRP

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.

(17 Dec '20, 12:46) Volker Barth

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
permanent link

answered 17 Dec '20, 20:07

bernie_chale's gravatar image

bernie_chale
415
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×16
×7

question asked: 16 Dec '20, 15:55

question was seen: 128 times

last updated: 17 Dec '20, 20:07