SQL Selecting dates with maximum sale for each department [duplicate]

Question

I am troubled with writing a tricky query.

I have the following table:

Tables

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.

Result

How should I fix it?

Fahmi · Accepted Answer · 2019-11-11 08:02:19Z

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

Popeye · Accepted Answer · 2019-11-11 08:28:29Z

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!!

Kira · Accepted Answer · 2019-11-11 08:21:54Z

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

This will not work as date_sale in not in the GROUP BY clause nor in an aggregation function.

Collectives™ on Stack Overflow

SQL Selecting dates with maximum sale for each department [duplicate]

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related