How to get best sellers per headquarters Hi i need to get the best seller of all products per headquarters: 

I have this query: 

SELECT Employees.first_name, Employees.last_name
     , Sold_products.product_type, Headquarters.country
     , Headquarters.city, Headquarters.street
     , COUNT(*) AS TOTAL
FROM Sold_products
   , Employees
   , Headquarters
WHERE Employees.id = Sold_products.employer_id 
  AND Employees.headquarters_id = Headquarters.id
GROUP BY Employees.first_name, Employees.last_name
       , Sold_products.product_type, Headquarters.country
       , Headquarters.city, Headquarters.street
ORDER BY Headquarters.country DESC, TOTAL DESC


I want to get result like: | first_name | last_name | Amount of all sold products | country | city | street

And Group it per unique Headquarter - Headquarter in db can have same city and country but not street - streets are unique (e.g. I have many Headquartes in German, Berlin but each have unique street)
To get the best selling item per headquarter, you first need to calculate the total sales per product per HQ and then rank them:

WITH TotalSalesByProductAndHq
AS
(
    SELECT h.id AS hq_id, p.product_type, SUM(p.value) AS TotalSales
      FROM Sold_products AS p
      JOIN Employees AS e
        ON e.id = p.employer_id
      JOIN Headquarters AS h
        ON e.headquarters_id = h.id
  GROUP BY h.id, p.product_type
),
RankedSalesByProductAndHq
AS
(
    SELECT hq_id, product_type
         , RANK() OVER(PARTITION BY hq_id ORDER BY TotalSales DESC) AS rnk
      FROM TotalSalesByProductAndHq
)
SELECT h.country
     , h.city
     , h.street
     , r.product_type
  FROM RankedSalesByProductAndHq AS r
  JOIN Headquarters AS h
    ON e.headquarters_id = r.hq_id
 WHERE r.rnk = 1;


Once you are able to do this, you can then bring back in the employee details to obtain the number of products sold in that category per employee:

WITH TotalSalesByProductAndHq
AS
(
    SELECT h.id AS hq_id, p.product_type, SUM(p.value) AS TotalSales
      FROM Sold_products AS p
      JOIN Employees AS e
        ON e.id = p.employer_id
      JOIN Headquarters AS h
        ON e.headquarters_id = h.id
  GROUP BY h.id, p.product_type
),
RankedSalesByProductAndHq
AS
(
    SELECT hq_id, product_type
         , RANK() OVER(PARTITION BY hq_id ORDER BY TotalSales DESC) AS rnk
      FROM TotalSalesByProductAndHq
)
SELECT e.first_name
     , e.last_name
     , h.country
     , h.city
     , h.street
     , r.product_type
     , COUNT(*) AS TotalNumberOfProductsSold
    FROM Sold_products AS p
    JOIN Employees AS e
      ON e.id = p.employer_id
    JOIN Headquarters AS h
      ON e.headquarters_id = h.id
    JOIN r
      ON r.hq_id = h.id
     AND r.product_type = p.product_type
 WHERE r.rnk = 1;


You should check out the documentation for CTE's and the RANK function for more details.