TSQL - Group by and sum not grouped column

Question

I'm facing some kind of problem. I have table "Prices" with columns - ProductId, ShopId, Date, Price. Table contains history of prices for products in diffrent shops. Each product can be in diffrent shop with diffrent price and Date.

I want to get sum of the lastest prices in all shops for each product.

| ProductId | ShopId |   Date     |  Price |
|:---------:|:------:|:----------:|:------:|
|     1     |   1    | 2020.11.10 |  100   |
|     1     |   2    | 2020.11.10 |  120   |
|     2     |   3    | 2020.11.10 |  200   |
|     3     |   3    | 2020.10.05 |  170   |
|     4     |   4    | 2020.11.10 |  200   |
|     4     |   4    | 2019.09.05 |  250   |

The output I want to get is (ShopId and Date can be included in output):

| ProductId | PriceSum |
|:---------:|:--------:|
|     1     |    220   |
|     2     |    200   |
|     3     |    170   |
|     4     |    200   |

I have following query:

SELECT ProductId, ShopId, MAX(Date) as MaxDate
FROM Prices
GROUP BY ShopId, ProductId
ORDER BY ProductId

Get the [Top 1 row of each group](https://stackoverflow.com/q/6841605/2029983)/[last row in each group](https://stackoverflow.com/q/4751913/2029983) and then `SUM`. — Thom A, Nov 18 '20 at 09:53

Lenroy Yeung · Answer 1 · 2020-11-19T03:17:38.287

0

Use window function to identity the latest dates and filter out older recs

;With dat
As (SELECT ProductId, ShopId, Date , Price
         , row_number() over partition by prodictid, date order by date desc)r
FROM Prices)

Select Productid
           , sum(price) Pricesum
From dat
Where rid=1
Group by productid;

edited Nov 19 '20 at 03:17

answered Nov 18 '20 at 11:03

Lenroy Yeung

291
3
8

I don't think you want to partition by `Date` here. – iamdave Nov 18 '20 at 11:51
2

Please add some explanation to your answer such that others can learn from it – Nico Haase Nov 18 '20 at 14:34

score 0 · Accepted Answer · answered Nov 18 '20 at 11:47

I've found solution, not the fastest but working.

 select st.ProductId, SUM(st.Price)
 from Prices as p1 
 cross apply 
     (
        select ProductId, ShopId, MAX(Date) as MaxDate
        from Prices
        group by ShopId, ProductId
     ) as p2
 where p2.MaxDate = p1.Dt 
    and p2.Shopid = p1.ShopId
    and p2.ProductId = p1.ProductId
 group by p1.ProductId 
 order by p1.ProductId

score 0 · Answer 3 · answered Nov 18 '20 at 19:53

In your case DENSE RANK window function can help you. If two or more rows have the same rank value in the same partition, each of those rows will receive the same rank.

WITH LatestPricesCTE AS
(
    SELECT *, DENSE_RANK() OVER (PARTITION BY ProductID ORDER BY Date DESC) Rank
    FROM Prices
)
SELECT ProductId, SUM(Price) PriceSum
FROM LatestPricesCTE
WHERE Rank = 1
GROUP BY ProductId

For more information: https://learn.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-ver15

TSQL - Group by and sum not grouped column

3 Answers3