Clever Geek Handbook

Select and copy the database of the latest records for the order.

I have different versions of the charges in the table. I want to capture and summarize the last charge, grouped by type.

So, I want to add 9.87, 9.63, 1.65.

I want to get the parent id, amount (9.87 + 9.63 + 1.65) as the results of this query.

We use MSSQL

ID  ORDER   CHARGES     TYPE    PARENT ID           
1   1       6.45        1       1
2   2       1.25        1       1
3   3       9.87        1       1
4   1       6.54        2       1
5   2       5.64        2       1
6   3       0.84        2       1
7   4       9.63        2       1
8   1       7.33        3       1
9   2       5.65        3       1
10  3       8.65        3       1
11  4       5.14        3       1
12  5       1.65        3       1
+3


source to share


4 answers 


WITH recordsList
AS
(
    SELECT Type, Charges,
            ROW_NUMBER() OVER (PArtition BY TYPE
                                ORDER BY [ORDER] DESC) rn
    FROM tableName
)
SELECT  SUM(Charges) totalCharge
FROM recordsLIst
WHERE rn = 1


+4


source


Use row_number()

to identify the lines to be summed and then sum them up:



select SUM(charges)
from (select t.*,
             ROW_NUMBER() over (PARTITION by type order by id desc) as seqnum
      from t
     ) t
where seqnum = 1
+3


source


Alternatively, you can use window aggregate MAX()

:

SELECT SUM(Charges)
FROM (
  SELECT
    [ORDER],
    Charges,
    MaxOrder = MAX([ORDER]) OVER (PARTITION BY [TYPE])
  FROM atable
) s
WHERE [ORDER] = MaxOrder
;
+1


source 


SELECT t.PARENT_ID, SUM(t.CHARGES)
FROM dbo.test73 t
WHERE EXISTS (
              SELECT 1
              FROM dbo.test73
              WHERE [TYPE] = t.[TYPE]
              HAVING MAX([ORDER]) = t.[ORDER]
              )
GROUP BY t.PARENT_ID

Demo on SQLFiddle

0


source






More articles:


All Articles