SQL Selecting Dates with Maximum Sale for Each Department

SQL Selecting dates with maximum sale for each department

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

Query to Show Max Sales for Each Seller

With ROW_NUMBER() window function:

SELECT t.Name_Seller, t.Month, t.Value
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Name_Seller ORDER BY Value DESC) rn
  FROM SALES
) t
WHERE t.rn = 1

Change ROW_NUMBER() with RANK() if you want ties returned.

Or with a correlated subquery in the WHERE clause:

SELECT s.* FROM SALES s
WHERE s.Value = (SELECT MAX(VALUE) FROM SALES WHERE Name_Seller = s.Name_Seller)

Or if your database supports it:

SELECT * FROM SALES
WHERE (Name_Seller, Value) IN (SELECT Name_Seller, MAX(VALUE) FROM SALES GROUP BY Name_Seller)

Select top 2 sold products for each month in each department

All non-aggregated columns have to be part of the GROUP BY clause; therefore, as commented it should be moved into the subquery. Also, you forgot to include the year = 2016 condition.

SELECT
    t.dep,
    t.month,
    t.prod,
    t.sales
FROM
    (
        SELECT
            geo.dept         AS dep,
            time.month       AS month,
            sales.prod_id    AS prod,
            SUM(sales.sales) AS sales,
            RANK()
            OVER(PARTITION BY geo.dept, time.month
                 ORDER BY
                     SUM(sales.sales) DESC NULLS LAST
            )                AS rank
        FROM
                 sales
            INNER JOIN time ON sales.time_id = time.time_id
            INNER JOIN geo ON sales.geo_id = geo.geo_id
        WHERE
            time.year = 2016
        GROUP BY
            geo.dept,
            time.month,
            sales.prod_id
    ) t
WHERE
    t.rank <= 2

SQL: Select most recent date for each category

SELECT
    category_a,
    category_b,
    MAX(date)
FROM
    Some_Unnamed_Table
GROUP BY
    category_a,
    category_b
ORDER BY
    category_a,
    category_b

I certainly don't mind helping people when I can, or I wouldn't be on this site, but did you really search for an answer for this before posting?

SQL query for total sales by each department for each day not working

You're grouping by date and time, meaning if they are not identical, they will not be grouped. To group based on only date, you'll have to convert TransactionDateTime to a Date, and do your selection/grouping on that instead.

SELECT SUM(Price) AS SalesTotal
    , Department
    , StoreNumber
    , CONVERT(date, SaleDateTime) As SaleDate
INTO #tblSales
FROM LineItem
WHERE SaleDateTime  BETWEEN @StartDate AND @EndDate
GROUP BY  Department,  T.StoreNumber, CONVERT(date, SaleDateTime)

SELECT *
FROM #tblSales

You're supplying slightly conflicting information, being that your query is using a field called SaleDateTime, yet your example table uses TransactionDateTime. The example above uses SaleDateTime.

Select EMP with max SAL from each DEPT

Classic greatest-n-per-group query. Here is what you want:

select dept.dname, emp.empno, emp.ename, emp.sal
from emp
inner join dept on emp.deptno = dept.deptno
inner join
(
select emp.deptno, max(emp.sal) sal
from emp
group by emp.deptno
) ss on emp.deptno = ss.deptno and emp.sal = ss.sal
order by emp.sal desc

Here is a working fiddle: http://sqlfiddle.com/#!4/7147b/6

Additionally, you might want to checkout a different approach. Look here (SQL Select only rows with Max Value on a Column) to see an interesting answer on the topic.

---

Related Topics