CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Arm_M
Arm_M

Reputation: 93

SQL Server grouping

I've got 'sales' table where I have stor_id, ord_date, and quantity. The problem is to find the stor_id which has maximum quantity for that ord_date

I used below query

select sales.ord_date, sales.stor_id, sum(sales.qty)
from sales 
group by sales.ord_date, sales.stor_id
order by sales.ord_date desc

In the result i got for example, that on 2016-12-04 and stor_id = 1 , the store has sold 35 books, and stor_id = 2 shop has sold 20 books for that same day.

I'd like to take the maximum value (in this case stor_id = 1 as it sold 35) of each group with the same date.

Upvotes: 2

Views: 64

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think the simplest method is to use window functions:

select s.*
from (select s.ord_date, s.stor_id, sum(s.qty) as sumqty,
             row_number() over (partition by s.ord_date order by sum(s.qty) desc) as seqnum
      from sales s
      group by s.ord_date, s.stor_id
    ) s
where seqnum = 1
order by s.ord_date desc;

Note that in the event of ties, this returns one store. If you want all of them, use rank() or dense_rank() instead of row_number().

Upvotes: 1

Mahedi Sabuj
Mahedi Sabuj

Reputation: 2944

As you need to find the store_id which has maximum quantity for that ord_date. To get this, we need to follow these step.

  • Find Summation of quantity for every ord_date and stor_id group

    -- lets think (for few seconds) we store the result in CTE_Total_Sales_Per_Day table
SELECT 
   stor_id,
   ord_date,
   SUM(qty) AS total_quantity
FROM Sales
GROUP BY ord_date, stor_id

  • Find maximum quantity of that ord_date

    -- lets think (for few seconds) we store the result in CTE_Maximum_Sales_Per_Day table
SELECT 
   ord_date,
   MAX(total_quantity) AS max_quantity
FROM CTE_Total_Sales_Per_Day 
GROUP BY ord_date

  • Join these two tables by ord_date and quantity

    SELECT 
   CTSPD.* 
FROM CTE_Total_Sales_Per_Day CTSPD
INNER JOIN CTE_Maximum_Sales_Per_Day CMSPD
 ON CTSPD.ord_date = CMSPD.ord_date
   AND CTSPD.max_quantity = CMSPD.total_quantity

Here goes the final query

;WITH CTE_Total_Sales_Per_Day (stor_id, ord_date, total_quantity)
AS
(
    SELECT stor_id, ord_date, SUM(qty) AS total_quantity FROM Sales
    GROUP BY ord_date, stor_id
)
,CTE_Maximum_Sales_Per_Day(ord_date, max_quantity)
AS
(
   SELECT ord_date, MAX(total_quantity) AS max_quantity
   FROM CTE_Total_Sales_Per_Day
   GROUP BY ord_date
)
,CTE_Maximum_Sales_Store_Id_Per_Day (stor_id, ord_date, total_quantity)
AS
(
    SELECT 
       CTSPD.* 
    FROM CTE_Total_Sales_Per_Day CTSPD
    INNER JOIN CTE_Maximum_Sales_Per_Day CMSPD
     ON CTSPD.ord_date = CMSPD.ord_date
       AND CTSPD.max_quantity = CMSPD.total_quantity
)

SELECT * FROM CTE_Maximum_Sales_Store_Id_Per_Day

At last, here i use WITH Common Table Expression. To learn about this, you can take a look at here WITH Common Table Expression

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can use the below query of you are using sql server 2008 above

select * from
(
select 
ord_date, 
case 
  when s.tot_sales = max(s.tot_sales) over (partition by s.ord_date) 
  then s.stor_id 
  else NULL 
end as store_id, 
max(s.tot_sales) over (partition by s.ord_date) as maximum_sales 
from
(select distinct ord_date, stor_id, 
sum(qty) over (partition by ord_date, stor_id) as tot_sales
from sales 
)s
)s
where s.store_id is not NULL

Working SQL fiddle:http://sqlfiddle.com/#!3/179e1/8

For other versions of SQL server

select sales.* from 
    (
        select  ord_date, stor_id, 
        sum(qty) as tot_sales
        from sales 
        group by ord_date, stor_id
    )sales
    inner join
    (
        select 
        ord_date, 
        max(s.tot_sales) as maximum_sales 
        from
        (
            select  ord_date, stor_id, 
            sum(qty) as tot_sales
            from sales 
            group by ord_date, stor_id
        )s
        group by ord_date 
    )s  
    on sales.ord_date=s.ord_date and sales.tot_sales=s.maximum_sales

Working sql fiddle: http://sqlfiddle.com/#!3/179e1/9

Upvotes: 2

Related Questions