how to add data two colums add after add two add next

Question

i have following query i want add two records like 16+16 = 32 + 15 = 47 like this i wANT 30 + 15 , = 47 + 15 like this ,

WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY gps_time) AS rn
        FROM    rawtTackHistory_A2Z where car_id = 12956 
        )
SELECT  mc.gps_time,DATEDIFF(second, mc.gps_time, mp.gps_time)
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1

I have no idea what you are describing. Can you provide desired results? — Gordon Linoff, May 07 '16 at 12:07
i want add 16 + 16 = 32 and then plus scond value like 32+ 15 then 47 + 15 — Adeel Khan, May 07 '16 at 12:13
dear i want add new column and, this column have sum values of timediff — Adeel Khan, May 07 '16 at 12:23
You can use a calculator to add numbers. Do you want to add rows to the table? Do you want a query that returns 32? I'm really not following. Desired results would help a lot. — Gordon Linoff, May 07 '16 at 12:28
One suspects that you have some rule for the calculation. I can't figure it out. — Gordon Linoff, May 07 '16 at 13:54

score 1 · Accepted Answer · edited May 23 '17 at 11:52

1

I'm not 100% sure what you're asking, but it sounds like you want a cumulative sum. That's a question that's been answered already:

https://stackoverflow.com/a/2120639/2565840

EDIT: in your case I think the query below should work

WITH    
rows AS (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY gps_time) AS rn
        FROM    rawtTackHistory_A2Z where car_id = 12956 
),
differences AS (
    SELECT  mc.rn, mc.gps_time,DATEDIFF(second, mc.gps_time, mp.gps_time) time_diff
    FROM    rows mc
    JOIN    rows mp
    ON      mc.rn = mp.rn - 1
)
SELECT t1.gps_time, t1.time_diff, SUM(t2.time_diff) time_sum
FROM differences t1
INNER JOIN differences t2 
ON t1.rn >= t2.rn
GROUP BY t1.rn, t1.gps_time, t1.time_diff
ORDER BY t1.rn

or if you're using SQL Server 2012 or later, this should run quicker:

SELECT gps_time
     , DATEDIFF(second, LAG(gps_time) OVER (ORDER BY gps_time), gps_time) time_diff
     , DATEDIFF(second, MIN(gps_time) OVER (ORDER BY gps_time), gps_time) time_sum
FROM rawtTackHistory_A2Z 
ORDER BY gps_time

It's using a windowing clause (OVER). More detail here: https://msdn.microsoft.com/en-us/library/ms189461.aspx

edited May 23 '17 at 11:52

Community

1
1

answered May 07 '16 at 12:18

velsietis

73
6

dear i want add new column and, this column have sum values of timediff – Adeel Khan May 07 '16 at 12:23
I've added an example. Is that what you're trying to do? – velsietis May 07 '16 at 12:27
error Column 'differences.time_diff' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Adeel Khan May 07 '16 at 12:53
dear i have 100000 records its to slow and continuously execution query – Adeel Khan May 07 '16 at 15:05
I've added an alternative method, which will work in SQL Server 2012 and later using a windowing clause – velsietis May 07 '16 at 15:19
please do me a favor i want add CASE where i add this because if moving status stop ones again, else start – Adeel Khan May 07 '16 at 15:42

how to add data two colums add after add two add next

1 Answers1