sum of running total from other partition (grouping sets?)
-
I need to count the items between one lead row and other, here an example:
I have this query:
SELECT td.countwinnerprice5to8 AS column1,
td.countwinnernotprice5to8 AS column2,
LEAD(td.sumwinnerprice5to8, 1) OVER(PARTITION BY countwinnerprice5to8
ORDER BY expr1003 DESC,
raceno DESC,
event_id DESC) AS lead2,
'' AS expected
FROM tbldata5 td
ORDER BY expr1003 DESC,
raceno DESC,
event_id DESC;which returns the following table: (with expected as my expected values)
+--------------------------------------+
¦ column1 ¦ column2 ¦ lead2 ¦ expected ¦
¦---------+---------+-------+----------¦
¦ 1 ¦ 2 ¦ 5.12 ¦ 4 ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 0 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 1 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 1 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 0 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 1 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 0 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 0 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 1 ¦ 1 ¦ 6.00 ¦ 9 ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 2 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 2 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 1 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 0 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 1 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 1 ¦ 3 ¦ 6.03 ¦ 3 ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 1 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 0 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 1 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 0 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 1 ¦ 1 ¦ 6.20 ¦ 1 ¦
¦---------+---------+-------+----------¦
¦ 1 ¦ 1 ¦ NULL ¦ ¦
¦---------+---------+-------+----------¦
¦ 0 ¦ 0 ¦ NULL ¦ ¦
+--------------------------------------+I need to have in the **"expected"** column the sum of the **column2** for the range between two "1" of **"column1"** I'm adding a small pic to show
I've tried adding something like this:
sum(td.countwinnernotprice5to8) OVER(PARTITION BY countwinnerprice5to8 Order by expr1003 desc, raceno desc, event_id DESC
rows between unbounded preceding and unbounded following
) as expectedbut is not working.
I hope this information helps to understand
I understand that this might be possible to do with grouping sets, I'm trying to figure how though
-
it would be a lot easier if you supplied DDL, Sample Data and your expected results. Screen shots help, but not in setting up the data
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
the ddl for the table is actually huge, the table has over 800 fields.
the example is quite simple though, do you think you need more information?
the data is ordered by date (field expr1003 ), race number (raceno) and the event_id field
-
I adding another image to show what I'm needing:
I need to sum the numbers within the blue boxes and substract them from the red boxes in the "lead2" column.
right now, my problem is that I can't sum the numbers in the blue box
-
.. and we can't help you without any data. How about a thin horizontal slice of the table's data. Enough to reproduce the problem. It's really hard to execute a GIF in TSQL.
-
a gif? what are you watching at? I added tables, sample data, screenshots. what is what you need? the problem with data an expected results is there. am I missing something?
not being rude, just don't understand
-
The pictures are fine, but I'm not going to create DDL and sample data for you. You don't need to provide the table with 800 columns, just a temp table with some sample data. We can't give you accurate help without it.
It's as simple as
Create table #A(field, field)
insert into #A.....
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
now I understand. sorry for being slow 🙂 I thought you actually wanted the whole package. here it is the DDL for the problem and example data.
CREATE TABLE [dbo].[newtable](
[event_id] [float] NULL,
[race] [nvarchar](255) NULL,
[event] [nvarchar](255) NULL,
[date] [date] NULL,
[race number] [tinyint] NULL,
[count1] [int] NULL,
[sum1] [decimal](7, 2) NULL,
[count2] [int] NULL,
[sum2] [decimal](7, 2) NULL,
[lead2] [decimal](7, 2) NULL,
[countlead2] [int] NULL
) ON [PRIMARY]
GOand to fill it:
Insert Into [newtable] ([event_id],[race],[event],[date],[race number],[count1],[sum1],[count2],[sum2],[lead2],[countlead2])
Values (145719665,N'AUS / Flem (AUS) 21st Jul',N'R9 1000m Hcap','21-Jul-18 ',9,0,NULL,1,7.40,NULL,1)
,(145719663,N'AUS / Flem (AUS) 21st Jul',N'R8 2000m Hcap','21-Jul-18 ',8,0,NULL,0,NULL,NULL,0)
,(145719661,N'AUS / Flem (AUS) 21st Jul',N'R7 1600m 3yo','21-Jul-18 ',7,0,NULL,1,6.22,NULL,2)
,(145719659,N'AUS / Flem (AUS) 21st Jul',N'R6 1100m 3yo','21-Jul-18 ',6,0,NULL,1,7.60,NULL,3)
,(145719657,N'AUS / Flem (AUS) 21st Jul',N'R5 1800m 2yo','21-Jul-18 ',5,0,NULL,1,7.69,NULL,4)
,(145719655,N'AUS / Flem (AUS) 21st Jul',N'R4 1200m Hcap','21-Jul-18 ',4,0,NULL,1,5.90,NULL,5)
,(145719653,N'AUS / Flem (AUS) 21st Jul',N'R3 2500m Hcap','21-Jul-18 ',3,0,NULL,0,NULL,NULL,0)
,(145719651,N'AUS / Flem (AUS) 21st Jul',N'R2 1700m Hcap','21-Jul-18 ',2,1,6.67,1,6.56,8.00,6)
,(145719649,N'AUS / Flem (AUS) 21st Jul',N'R1 1400m 3yo','21-Jul-18 ',1,0,NULL,0,NULL,NULL,0)
,(145210614,N'AUS / Flem (AUS) 7th Jul',N'R9 1200m Listed','07-Jul-18 ',9,0,NULL,1,5.26,NULL,7)
,(145210612,N'AUS / Flem (AUS) 7th Jul',N'R8 1600m Listed','07-Jul-18 ',8,1,8.00,2,15.42,5.64,2)
,(145210610,N'AUS / Flem (AUS) 7th Jul',N'R7 1200m Listed','07-Jul-18 ',7,0,NULL,0,NULL,NULL,0)
,(145210608,N'AUS / Flem (AUS) 7th Jul',N'R6 2600m Hcap','07-Jul-18 ',6,0,NULL,2,12.65,NULL,4)
,(145210606,N'AUS / Flem (AUS) 7th Jul',N'R5 1600m Listed','07-Jul-18 ',5,0,NULL,0,NULL,NULL,0)
,(145210604,N'AUS / Flem (AUS) 7th Jul',N'R4 1600m 3yo','07-Jul-18 ',4,0,NULL,1,5.27,NULL,8)
,(145210602,N'AUS / Flem (AUS) 7th Jul',N'R3 1400m Hcap','07-Jul-18 ',3,1,5.64,1,7.68,5.51,9)
,(145210600,N'AUS / Flem (AUS) 7th Jul',N'R2 2000m Hcap','07-Jul-18 ',2,0,NULL,0,NULL,NULL,0)
,(145210598,N'AUS / Flem (AUS) 7th Jul',N'R1 1600m 3yo','07-Jul-18 ',1,0,NULL,3,21.97,NULL,3)
,(144827118,N'AUS / Flem (AUS) 23rd Jun',N'R9 1100m Hcap','23-Jun-18',9,0,NULL,1,5.33,NULL,10)
,(144827116,N'AUS / Flem (AUS) 23rd Jun',N'R8 1600m Hcap','23-Jun-18',8,0,NULL,1,6.67,NULL,11)
,(144827114,N'AUS / Flem (AUS) 23rd Jun',N'R7 1400m Hcap','23-Jun-18',7,0,NULL,0,NULL,NULL,0)
,(144827112,N'AUS / Flem (AUS) 23rd Jun',N'R6 1600m 3yo','23-Jun-18',6,0,NULL,2,13.03,NULL,6)
,(144827110,N'AUS / Flem (AUS) 23rd Jun',N'R5 2520m Hcap','23-Jun-18',5,0,NULL,2,14.56,NULL,8)
,(144827108,N'AUS / Flem (AUS) 23rd Jun',N'R4 1400m Hcap','23-Jun-18',4,1,5.51,3,20.44,7.80,6)
,(144827106,N'AUS / Flem (AUS) 23rd Jun',N'R3 1100m 3yo','23-Jun-18',3,0,NULL,2,11.94,NULL,10)
,(144827104,N'AUS / Flem (AUS) 23rd Jun',N'R2 1400m Hcap','23-Jun-18',2,0,NULL,1,7.29,NULL,12)
,(144827102,N'AUS / Flem (AUS) 23rd Jun',N'R1 1400m 2yo','23-Jun-18',1,0,NULL,3,19.39,NULL,9)
,(144506971,N'AUS / Flem (AUS) 9th Jun',N'R9 1000m 3yo','09-Jun-18',9,0,NULL,1,7.97,NULL,13)
,(144506969,N'AUS / Flem (AUS) 9th Jun',N'R8 2000m 3yo','09-Jun-18',8,0,NULL,3,20.10,NULL,12)
,(144506967,N'AUS / Flem (AUS) 9th Jun',N'R7 1400m Hcap','09-Jun-18',7,0,NULL,1,5.71,NULL,14)
,(144506965,N'AUS / Flem (AUS) 9th Jun',N'R6 1400m Hcap','09-Jun-18',6,0,NULL,1,5.69,NULL,15)
,(144506963,N'AUS / Flem (AUS) 9th Jun',N'R5 1000m Hcap','09-Jun-18',5,0,NULL,1,5.96,NULL,16)
,(144506961,N'AUS / Flem (AUS) 9th Jun',N'R4 1800m Hcap','09-Jun-18',4,0,NULL,1,8.00,NULL,17)
,(144506959,N'AUS / Flem (AUS) 9th Jun',N'R3 1400m 3yo','09-Jun-18',3,0,NULL,2,14.62,NULL,12)
,(144506957,N'AUS / Flem (AUS) 9th Jun',N'R2 2500m Hcap','09-Jun-18',2,0,NULL,1,5.28,NULL,18)
,(144506955,N'AUS / Flem (AUS) 9th Jun',N'R1 1100m 2yo','09-Jun-18',1,0,NULL,0,NULL,NULL,0)
,(143916424,N'AUS / Flem (AUS) 19th May',N'R9 1600m 3yo','19-May-18',9,0,NULL,0,NULL,NULL,0)
,(143916422,N'AUS / Flem (AUS) 19th May',N'R8 1400m Hcap','19-May-18',8,0,NULL,1,7.60,NULL,19)
,(143916420,N'AUS / Flem (AUS) 19th May',N'R7 3200m Listed','19-May-18',7,0,NULL,2,12.68,NULL,14)
,(143916418,N'AUS / Flem (AUS) 19th May',N'R6 1200m Listed','19-May-18',6,0,NULL,1,6.00,NULL,20)
,(143916416,N'AUS / Flem (AUS) 19th May',N'R5 2000m Hcap','19-May-18',5,0,NULL,1,6.06,NULL,21)
,(143916414,N'AUS / Flem (AUS) 19th May',N'R4 1800m Hcap','19-May-18',4,0,NULL,2,11.43,NULL,16)
,(143916412,N'AUS / Flem (AUS) 19th May',N'R3 1600m Hcap','19-May-18',3,0,NULL,1,8.00,NULL,22)
,(143916410,N'AUS / Flem (AUS) 19th May',N'R2 1200m 3yo','19-May-18',2,0,NULL,1,5.57,NULL,23)
,(143916408,N'AUS / Flem (AUS) 19th May',N'R1 1400m 2yo','19-May-18',1,0,NULL,2,12.40,NULL,18)
,(143405684,N'AUS / Flem (AUS) 5th May',N'R9 1400m 3yo','05-May-18',9,0,NULL,0,NULL,NULL,0)
,(143405682,N'AUS / Flem (AUS) 5th May',N'R8 1800m Hcap','05-May-18',8,1,7.80,2,12.61,7.77,20)
,(143405680,N'AUS / Flem (AUS) 5th May',N'R7 1400m Hcap','05-May-18',7,0,NULL,1,6.40,NULL,24)
,(143405678,N'AUS / Flem (AUS) 5th May',N'R6 1600m Hcap','05-May-18',6,0,NULL,1,5.30,NULL,25)
,(143405676,N'AUS / Flem (AUS) 5th May',N'R5 1000m Hcap','05-May-18',5,0,NULL,2,14.92,NULL,22)
,(143405674,N'AUS / Flem (AUS) 5th May',N'R4 1700m 3yo','05-May-18',4,1,7.77,2,13.35,5.10,24)
,(143405672,N'AUS / Flem (AUS) 5th May',N'R3 2000m Hcap','05-May-18',3,0,NULL,3,19.57,NULL,15)
,(143405670,N'AUS / Flem (AUS) 5th May',N'R2 2800m Hcap','05-May-18',2,1,5.10,1,5.63,7.10,26)
,(143405668,N'AUS / Flem (AUS) 5th May',N'R1 1100m 2yo','05-May-18',1,1,7.10,2,10.79,5.44,26)
,(143033338,N'AUS / Flem (AUS) 25th Apr',N'R8 1200m Hcap','25-Apr-18',8,1,5.44,0,NULL,5.90,0)
,(143033336,N'AUS / Flem (AUS) 25th Apr',N'R7 1400m 3yo','25-Apr-18',7,0,NULL,1,7.00,NULL,27)
,(143033334,N'AUS / Flem (AUS) 25th Apr',N'R6 2800m Listed','25-Apr-18',6,0,NULL,1,7.72,NULL,28)
,(143033332,N'AUS / Flem (AUS) 25th Apr',N'R5 1720m 3yo','25-Apr-18',5,0,NULL,1,5.31,NULL,29)
,(143033330,N'AUS / Flem (AUS) 25th Apr',N'R4 1620m Hcap','25-Apr-18',4,0,NULL,2,12.42,NULL,28)
,(143033328,N'AUS / Flem (AUS) 25th Apr',N'R3 1400m Listed','25-Apr-18',3,0,NULL,0,NULL,NULL,0)
,(143033326,N'AUS / Flem (AUS) 25th Apr',N'R2 1800m Hcap','25-Apr-18',2,1,5.90,0,NULL,5.20,0)
,(143033324,N'AUS / Flem (AUS) 25th Apr',N'R1 2530m Hcap','25-Apr-18',1,0,NULL,1,5.50,NULL,30)
,(141347592,N'AUS / Flem (AUS) 17th Mar',N'R9 1200m Hcap','17-Mar-18',9,0,NULL,2,12.36,NULL,30)
,(141347590,N'AUS / Flem (AUS) 17th Mar',N'R8 1600m Hcap','17-Mar-18',8,0,NULL,0,NULL,NULL,0)
,(141347588,N'AUS / Flem (AUS) 17th Mar',N'R7 1400m Listed','17-Mar-18',7,0,NULL,1,5.70,NULL,31)
,(141347586,N'AUS / Flem (AUS) 17th Mar',N'R6 2000m Hcap','17-Mar-18',6,0,NULL,0,NULL,NULL,0)
,(141347584,N'AUS / Flem (AUS) 17th Mar',N'R5 1600m Grp2','17-Mar-18',5,0,NULL,1,5.21,NULL,32)
,(141347582,N'AUS / Flem (AUS) 17th Mar',N'R4 1200m Grp3','17-Mar-18',4,0,NULL,0,NULL,NULL,0)
,(141347580,N'AUS / Flem (AUS) 17th Mar',N'R3 1100m 3yo','17-Mar-18',3,0,NULL,0,NULL,NULL,0)
,(141347578,N'AUS / Flem (AUS) 17th Mar',N'R2 1400m 3yo','17-Mar-18',2,1,5.20,1,6.60,7.40,33)
,(141347576,N'AUS / Flem (AUS) 17th Mar',N'R1 1200m Hcap','17-Mar-18',1,0,NULL,1,5.96,NULL,34)
,(141026701,N'AUS / Flem (AUS) 10th Mar',N'R9 1100m Listed','10-Mar-18',9,0,NULL,2,14.13,NULL,32)
,(140979519,N'AUS / Flem (AUS) 10th Mar',N'R8 2000m Grp1','10-Mar-18',8,0,NULL,0,NULL,NULL,0)
,(141026697,N'AUS / Flem (AUS) 10th Mar',N'R7 1600m Grp3','10-Mar-18',7,0,NULL,1,6.27,NULL,35)
,(140979517,N'AUS / Flem (AUS) 10th Mar',N'R6 1200m Grp1','10-Mar-18',6,1,7.40,2,13.99,5.98,34)
,(141026693,N'AUS / Flem (AUS) 10th Mar',N'R5 1400m Grp2','10-Mar-18',5,1,5.98,3,22.39,5.80,18)
,(141026691,N'AUS / Flem (AUS) 10th Mar',N'R4 1400m Grp2','10-Mar-18',4,0,NULL,3,19.70,NULL,21)
,(141026689,N'AUS / Flem (AUS) 10th Mar',N'R3 1400m Hcap','10-Mar-18',3,0,NULL,1,5.50,NULL,36)
,(141026687,N'AUS / Flem (AUS) 10th Mar',N'R2 1800m Hcap','10-Mar-18',2,0,NULL,0,NULL,NULL,0)
,(141026685,N'AUS / Flem (AUS) 10th Mar',N'R1 1000m Hcap','10-Mar-18',1,0,NULL,1,5.60,NULL,37)
,(140714383,N'AUS / Flem (AUS) 3rd Mar',N'R9 1400m 3yo','03-Mar-18',9,0,NULL,0,NULL,NULL,0)
,(140714381,N'AUS / Flem (AUS) 3rd Mar',N'R8 1000m Listed','03-Mar-18',8,0,NULL,1,5.10,NULL,38)
,(140687350,N'AUS / Flem (AUS) 3rd Mar',N'R7 1600m Grp1','03-Mar-18',7,1,5.80,1,6.71,7.00,39)
,(140714377,N'AUS / Flem (AUS) 3rd Mar',N'R6 1400m Grp3','03-Mar-18',6,0,NULL,2,12.19,NULL,36)
,(140714375,N'AUS / Flem (AUS) 3rd Mar',N'R5 2000m Hcap','03-Mar-18',5,0,NULL,1,6.40,NULL,40)
,(140714373,N'AUS / Flem (AUS) 3rd Mar',N'R4 1400m Grp3','03-Mar-18',4,0,NULL,0,NULL,NULL,0)
,(140714371,N'AUS / Flem (AUS) 3rd Mar',N'R3 1100m Hcap','03-Mar-18',3,0,NULL,1,6.66,NULL,41)
,(140714369,N'AUS / Flem (AUS) 3rd Mar',N'R2 2600m Listed','03-Mar-18',2,0,NULL,2,10.58,NULL,38)
,(140714367,N'AUS / Flem (AUS) 3rd Mar',N'R1 1000m Listed','03-Mar-18',1,0,NULL,2,14.44,NULL,40)
,(140134144,N'AUS / Flem (AUS) 17th Feb',N'R9 1400m Hcap','17-Feb-18',9,0,NULL,1,7.69,NULL,42)
,(140134142,N'AUS / Flem (AUS) 17th Feb',N'R8 1000m Grp1','17-Feb-18',8,0,NULL,0,NULL,NULL,0)
,(140134140,N'AUS / Flem (AUS) 17th Feb',N'R7 1400m Grp3','17-Feb-18',7,1,7.00,2,13.37,5.07,42)
,(140134138,N'AUS / Flem (AUS) 17th Feb',N'R6 1400m Grp3','17-Feb-18',6,0,NULL,3,18.83,NULL,24)
,(140134136,N'AUS / Flem (AUS) 17th Feb',N'R5 1200m Hcap','17-Feb-18',5,1,5.07,1,5.60,5.06,43)
,(140134134,N'AUS / Flem (AUS) 17th Feb',N'R4 1600m Hcap','17-Feb-18',4,0,NULL,1,7.20,NULL,44)
,(140134132,N'AUS / Flem (AUS) 17th Feb',N'R3 1400m Hcap','17-Feb-18',3,1,5.06,1,6.29,5.10,45)
,(140134130,N'AUS / Flem (AUS) 17th Feb',N'R2 2000m Hcap','17-Feb-18',2,0,NULL,2,14.04,NULL,44)
,(140134128,N'AUS / Flem (AUS) 17th Feb',N'R1 1100m Listed','17-Feb-18',1,0,NULL,2,12.53,NULL,46)
,(139022582,N'AUS / Flem (AUS) 20th Jan',N'R9 1400m Hcap','20-Jan-18',9,1,5.10,1,6.45,7.40,46)
,(139022580,N'AUS / Flem (AUS) 20th Jan',N'R8 1000m Listed','20-Jan-18',8,0,NULL,1,7.41,NULL,47)
,(139022578,N'AUS / Flem (AUS) 20th Jan',N'R7 1100m 3yo','20-Jan-18',7,0,NULL,1,5.80,NULL,48)
,(139022576,N'AUS / Flem (AUS) 20th Jan',N'R6 1200m Hcap','20-Jan-18',6,0,NULL,1,6.31,NULL,49)
,(139022574,N'AUS / Flem (AUS) 20th Jan',N'R5 1600m Hcap','20-Jan-18',5,1,7.40,1,7.58,6.40,50)
,(139022572,N'AUS / Flem (AUS) 20th Jan',N'R4 2000m Hcap','20-Jan-18',4,0,NULL,1,7.00,NULL,51)
,(139022570,N'AUS / Flem (AUS) 20th Jan',N'R3 2000m 3yo','20-Jan-18',3,1,6.40,0,NULL,5.34,0)
,(139022568,N'AUS / Flem (AUS) 20th Jan',N'R2 2500m Hcap','20-Jan-18',2,0,NULL,1,7.60,NULL,52)
,(139022566,N'AUS / Flem (AUS) 20th Jan',N'R1 1800m Hcap','20-Jan-18',1,0,NULL,2,11.47,NULL,48)
,(138763456,N'AUS / Flem (AUS) 13th Jan',N'R9 1400m Hcap','13-Jan-18',9,0,NULL,0,NULL,NULL,0)
,(138763454,N'AUS / Flem (AUS) 13th Jan',N'R8 1100m Hcap','13-Jan-18',8,1,5.34,0,NULL,7.94,0)
,(138763452,N'AUS / Flem (AUS) 13th Jan',N'R7 1400m Listed','13-Jan-18',7,1,7.94,1,5.12,6.51,53)
,(138763450,N'AUS / Flem (AUS) 13th Jan',N'R6 1100m Hcap','13-Jan-18',6,0,NULL,2,13.03,NULL,50)
,(138763448,N'AUS / Flem (AUS) 13th Jan',N'R5 1400m 3yo','13-Jan-18',5,1,6.51,1,7.57,7.27,54)
,(138763446,N'AUS / Flem (AUS) 13th Jan',N'R4 1400m 3yo','13-Jan-18',4,0,NULL,2,14.63,NULL,52)
,(138763444,N'AUS / Flem (AUS) 13th Jan',N'R3 1700m Hcap','13-Jan-18',3,0,NULL,0,NULL,NULL,0)
,(138763442,N'AUS / Flem (AUS) 13th Jan',N'R2 2000m Hcap','13-Jan-18',2,0,NULL,4,28.12,NULL,4)
,(138763440,N'AUS / Flem (AUS) 13th Jan',N'R1 1000m 2yo','13-Jan-18',1,1,7.27,1,6.80,6.20,55)
,(138386470,N'AUS / Flem (AUS) 1st Jan',N'R8 1400m Hcap','01-Jan-18',8,1,6.20,0,NULL,NULL,0)
,(138386468,N'AUS / Flem (AUS) 1st Jan',N'R7 2800m Listed','01-Jan-18',7,0,NULL,1,5.55,NULL,56)
,(138386466,N'AUS / Flem (AUS) 1st Jan',N'R6 1200m Grp3','01-Jan-18',6,0,NULL,3,19.46,NULL,27)
,(138386464,N'AUS / Flem (AUS) 1st Jan',N'R5 1700m Hcap','01-Jan-18',5,0,NULL,3,20.74,NULL,30)
,(138386462,N'AUS / Flem (AUS) 1st Jan',N'R4 2000m Hcap','01-Jan-18',4,0,NULL,1,5.33,NULL,57)
,(138386460,N'AUS / Flem (AUS) 1st Jan',N'R3 2000m Hcap','01-Jan-18',3,0,NULL,3,18.96,NULL,33)
,(138386458,N'AUS / Flem (AUS) 1st Jan',N'R2 1400m Hcap','01-Jan-18',2,0,NULL,2,11.55,NULL,54)
,(138386456,N'AUS / Flem (AUS) 1st Jan',N'R1 1000m 2yo','01-Jan-18',1,0,NULL,3,18.41,NULL,36) -
The DDL works, but the data doesn't. also, what are you expecting as a result from the data you provided
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
I have fixed the insert.
basically what I need to do is to summarize the count of column "count2" for a every partition, and then subtract it to the field in "lead2" so, with the colors, sum what is on blue box, and subtract from red box in lead2.
so form this example, in a new column, put
5.64 - (0+1+2)
5.51 - (0+2+0+1+1)
7.80- (0+3+1+1+0+2+2+3)
this can be worked maybe with CTE for the grouping and summarizing of the count2. but I needed it to be as simplea s possible, since I need to "expand" this calculation for hundreds of columns that need this similar calculation (these are columns with category counts and price brackets)
Thank you Mike for taking the time to help me. I really appreciate it
- Baldie47 wrote:
basically what I need to do is to summarize the count of column "count2" for a every partition, and then subtract it to the field in "lead2" so, with the colors, sum what is on blue box, and subtract from red box in lead2.
From my perspective I'm not understanding how the partition window is defined. Which columns make up the partition?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
the count1 with a lag() function. since it has to get from the first "1" to the second "1" and so on
-
Sorry I should've also asked which are the ORDER BY column(s)? The window has 2 parts 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
sure thing, the order by is for
ORDER BY expr1003 DESC,
raceno DESC,
event_id DESC
-
In the DDL provided:
[event_id] [float] NULL
[race number] [tinyint] NULLAre these 2 of the 3 columns to be included in the ORDER BY? Which column corresponds to expr1003?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply