CODEWITHSUNDEEP
sql-serversql-server-2008-r2
sai bharath
sai bharath

Reputation: 844

Getting sum value of a column having minimum date?

I have data like this:

DATE                       ID        weight
----                      ----      -------
2017-04-25 11:05:42.273    247       0.418
2017-04-25 11:05:42.310    248       0.568
2017-04-25 13:57:55.327    247       0.418
2017-04-25 13:57:55.360    247       0.534
2017-04-25 13:57:55.397    248       0.568
2017-04-25 13:57:55.453    248       0.448

Now the requirement is I have to sum the gross weight based on barcodeid having minimum date.

here the output should be (0.418+0.568) because it has minimum date for barcode 247 and 248 respectively.

Upvotes: 0

Views: 1043

Answers (3)

xQbert
xQbert

Reputation: 35323

Use a window function to assign a row number starting over for each partition (ID) then only sum rownumber 1 a CTE or subquery is needed since RN would not be available to limit by.

A partition is just a grouping of records in the columns specified. so ID where 247 and 248 are different groups and row #1 will be assigned to the earliest date in each partition. Then when we say where rn = 1 we only get weights for those earliest dates of each different ID!

WITH CTE AS (SELECT A.* 
                    , Row_NUMBER() Over (Partition by ID order by Date asc) RN 
             FROM TABLE A)
SELECT Sum(Weight) 
FROM CTE 
WHERE RN = 1

Upvotes: 3

SCFi
SCFi

Reputation: 522

Edit: Well I have egg on my face. Fixed

I believe a simple sub query will suffice

SELECT sum(weight)
FROM Table t1
WHERE DATE = (select min(DATE) from Table t2 where t1.ID = t2.ID group by id)

Upvotes: 1

user7715598
user7715598

Reputation: 

;With cte([DATE],ID,[weight])
AS
(

SELECT '2017-04-25 11:05:42.273', 247, 0.418 Union all
SELECT '2017-04-25 11:05:42.310', 248, 0.568 Union all
SELECT '2017-04-25 13:57:55.327', 247, 0.418 Union all
SELECT '2017-04-25 13:57:55.360', 247, 0.534 Union all
SELECT '2017-04-25 13:57:55.397', 248, 0.568 Union all
SELECT '2017-04-25 13:57:55.453', 248, 0.448
)
SELECT Sum(MinWeight) [SumOFweight] From
(
SELECT  ID,DATE,Min([weight])OVER(Partition by DATE) AS MinWeight ,Row_NUMBER() Over (Partition by ID order by Date asc) RN From
(
SELECT DATE,ID,SUM([weight])[weight]  FROM cte
GROUP by ID,DATE
)dt
)Final
where Final.RN=1

OutPut

SumOFweight
-------------
0.986

Upvotes: 0

Related Questions