SQL Selecting dates with maximum sale for each department

Question

I am troubled with writing a tricky query.

I have the following table:

For each department I want to print date with largest profit;

I tried coming up with such a query myself:

Select DISTINCT(Name), Date_sale, MAX(A) as B FROM (SELECT 
 Departments.Name, SALES.Date_sale, SUM(GOODS.Price * SALES.Quantity) 
 AS A FROM DEPARTMENTS, GOODS, SALES
 WHERE DEPARTMENTS.Dept_id = GOODS.Dept_id AND GOODS.Good_id = 
 SALES.Good_id GROUP BY DEPARTMENTs.Name, SALES.Date_sale) 
 GROUP BY Name, Date_sale;

But the problem it that departments are printed several times because I groupped by both name and date.

How should I fix it?

Answer 1

You can try below way-

with cte as 
(
 SELECT 
 Departments.Name, SALES.Date_sale, SUM(GOODS.Price * SALES.Quantity) 
 AS profit FROM DEPARTMENTS inner join GOODS on DEPARTMENTS.Dept_id = GOODS.Dept_id
 inner join SALES on GOODS.Good_id = SALES.Good_id
 GROUP BY DEPARTMENTs.Name, SALES.Date_sale
)A

select * from cte a
where profit =
     (select max(profit) from cte b on a.department=b.department)

OR you can use row_number()

select * from
(
select *, row_number() over(partition by department oder by profit desc) as rn
from cte
)A where rn=1

Answer 2

You can write it using ROW_NUMBER which will give a number to each date's total count grouped by the department as following and then you can take the highest sale date using rn = 1

SELECT NAME, DATE_SALE, A
FROM
    (
        SELECT
            DEPARTMENTS.NAME, SALES.DATE_SALE,
            ROW_NUMBER() OVER(
                PARTITION BY DEPARTMENTS.NAME
                ORDER BY SUM(GOODS.PRICE * SALES.QUANTITY) DESC NULLS LAST
            ) AS RN,
            SUM(GOODS.PRICE * SALES.QUANTITY) AS A
        FROM DEPARTMENTS
            JOIN GOODS ON ( DEPARTMENTS.DEPT_ID = GOODS.DEPT_ID )
            JOIN SALES ON ( GOODS.GOOD_ID = SALES.GOOD_ID )
        GROUP BY DEPARTMENTS.NAME,
            SALES.DATE_SALE
    )
WHERE RN = 1;

Important, Use the standard ANSI-joins.

Cheers!!

Answer 3

i would use join-s here as it is needed to pull info from 2 tables linked via the third table.

Something like this (but I have not tested this query, just suggesting an approach):

Select department.name as dept, MAX(sales.quantity) as max_sales, sales.date_sale 
  from goods
Left outer join departments on departments.dept_id = goods.dept_id
Left outer join sales on sales.good_id = goods.good_id
Group by dept