I need to make a sum of the count of all items in “count2” for the partitions, the screenshot below shows better than I could explain, but I need to sum the items in that column and then subtract them form the “Lead2” column I have the following query:
x
SELECT td.EVENT_ID AS event_id, td.MENU_HINT AS race, td.EVENT_NAME AS event, td.expr1003 AS [date], td.raceno AS [race number], td.countwinnerprice5to8 AS [count1], td.sumwinnerprice5to8 AS [sum1], td.countwinnernotprice5to8 AS [count2], td.sumwinnernotprice5to8 AS [sum2], lead(td.sumwinnerprice5to8,1) OVER(PARTITION BY countwinnerprice5to8 Order by expr1003 desc, raceno desc, event_id desc ) as lead2, sum(td.countwinnernotprice5to8) OVER(PARTITION BY countwinnernotprice5to8 Order by expr1003 desc, raceno desc, event_id DESC rows between 1 following and unbounded following ) as countlead2FROM tbldata5 tdWHERE menu_hint LIKE '%Flem%'ORDER BY expr1003 desc, raceno desc, event_id descwhich returns this table:
╔═══════════╦═══════════════════════════╦═════════════════╦════════════╦═════════════╦════════╦══════╦════════╦═══════╦═══════╦════════════╗║ event_id ║ race ║ event ║ date ║ race number ║ count1 ║ sum1 ║ count2 ║ sum2 ║ lead2 ║ countlead2 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145719665 ║ AUS / Flem (AUS) 21st Jul ║ R9 1000m Hcap ║ 2018-07-21 ║ 9 ║ 0 ║ NULL ║ 1 ║ 7.40 ║ NULL ║ 1 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145719663 ║ AUS / Flem (AUS) 21st Jul ║ R8 2000m Hcap ║ 2018-07-21 ║ 8 ║ 0 ║ NULL ║ 0 ║ NULL ║ NULL ║ 0 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145719661 ║ AUS / Flem (AUS) 21st Jul ║ R7 1600m 3yo ║ 2018-07-21 ║ 7 ║ 0 ║ NULL ║ 1 ║ 6.22 ║ NULL ║ 2 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145719659 ║ AUS / Flem (AUS) 21st Jul ║ R6 1100m 3yo ║ 2018-07-21 ║ 6 ║ 0 ║ NULL ║ 1 ║ 7.60 ║ NULL ║ 3 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145719657 ║ AUS / Flem (AUS) 21st Jul ║ R5 1800m 2yo ║ 2018-07-21 ║ 5 ║ 0 ║ NULL ║ 1 ║ 7.69 ║ NULL ║ 4 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145719655 ║ AUS / Flem (AUS) 21st Jul ║ R4 1200m Hcap ║ 2018-07-21 ║ 4 ║ 0 ║ NULL ║ 1 ║ 5.90 ║ NULL ║ 5 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145719653 ║ AUS / Flem (AUS) 21st Jul ║ R3 2500m Hcap ║ 2018-07-21 ║ 3 ║ 0 ║ NULL ║ 0 ║ NULL ║ NULL ║ 0 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145719651 ║ AUS / Flem (AUS) 21st Jul ║ R2 1700m Hcap ║ 2018-07-21 ║ 2 ║ 1 ║ 6.67 ║ 1 ║ 6.56 ║ 8.00 ║ 6 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145719649 ║ AUS / Flem (AUS) 21st Jul ║ R1 1400m 3yo ║ 2018-07-21 ║ 1 ║ 0 ║ NULL ║ 0 ║ NULL ║ NULL ║ 0 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145210614 ║ AUS / Flem (AUS) 7th Jul ║ R9 1200m Listed ║ 2018-07-07 ║ 9 ║ 0 ║ NULL ║ 1 ║ 5.26 ║ NULL ║ 7 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145210612 ║ AUS / Flem (AUS) 7th Jul ║ R8 1600m Listed ║ 2018-07-07 ║ 8 ║ 1 ║ 8.00 ║ 2 ║ 15.42 ║ 5.64 ║ 2 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145210610 ║ AUS / Flem (AUS) 7th Jul ║ R7 1200m Listed ║ 2018-07-07 ║ 7 ║ 0 ║ NULL ║ 0 ║ NULL ║ NULL ║ 0 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145210608 ║ AUS / Flem (AUS) 7th Jul ║ R6 2600m Hcap ║ 2018-07-07 ║ 6 ║ 0 ║ NULL ║ 2 ║ 12.65 ║ NULL ║ 4 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145210606 ║ AUS / Flem (AUS) 7th Jul ║ R5 1600m Listed ║ 2018-07-07 ║ 5 ║ 0 ║ NULL ║ 0 ║ NULL ║ NULL ║ 0 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145210604 ║ AUS / Flem (AUS) 7th Jul ║ R4 1600m 3yo ║ 2018-07-07 ║ 4 ║ 0 ║ NULL ║ 1 ║ 5.27 ║ NULL ║ 8 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145210602 ║ AUS / Flem (AUS) 7th Jul ║ R3 1400m Hcap ║ 2018-07-07 ║ 3 ║ 1 ║ 5.64 ║ 1 ║ 7.68 ║ 5.51 ║ 9 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145210600 ║ AUS / Flem (AUS) 7th Jul ║ R2 2000m Hcap ║ 2018-07-07 ║ 2 ║ 0 ║ NULL ║ 0 ║ NULL ║ NULL ║ 0 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 145210598 ║ AUS / Flem (AUS) 7th Jul ║ R1 1600m 3yo ║ 2018-07-07 ║ 1 ║ 0 ║ NULL ║ 3 ║ 21.97 ║ NULL ║ 3 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144827118 ║ AUS / Flem (AUS) 23rd Jun ║ R9 1100m Hcap ║ 2018-06-23 ║ 9 ║ 0 ║ NULL ║ 1 ║ 5.33 ║ NULL ║ 10 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144827116 ║ AUS / Flem (AUS) 23rd Jun ║ R8 1600m Hcap ║ 2018-06-23 ║ 8 ║ 0 ║ NULL ║ 1 ║ 6.67 ║ NULL ║ 11 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144827114 ║ AUS / Flem (AUS) 23rd Jun ║ R7 1400m Hcap ║ 2018-06-23 ║ 7 ║ 0 ║ NULL ║ 0 ║ NULL ║ NULL ║ 0 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144827112 ║ AUS / Flem (AUS) 23rd Jun ║ R6 1600m 3yo ║ 2018-06-23 ║ 6 ║ 0 ║ NULL ║ 2 ║ 13.03 ║ NULL ║ 6 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144827110 ║ AUS / Flem (AUS) 23rd Jun ║ R5 2520m Hcap ║ 2018-06-23 ║ 5 ║ 0 ║ NULL ║ 2 ║ 14.56 ║ NULL ║ 8 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144827108 ║ AUS / Flem (AUS) 23rd Jun ║ R4 1400m Hcap ║ 2018-06-23 ║ 4 ║ 1 ║ 5.51 ║ 3 ║ 20.44 ║ 7.80 ║ 6 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144827106 ║ AUS / Flem (AUS) 23rd Jun ║ R3 1100m 3yo ║ 2018-06-23 ║ 3 ║ 0 ║ NULL ║ 2 ║ 11.94 ║ NULL ║ 10 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144827104 ║ AUS / Flem (AUS) 23rd Jun ║ R2 1400m Hcap ║ 2018-06-23 ║ 2 ║ 0 ║ NULL ║ 1 ║ 7.29 ║ NULL ║ 12 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144827102 ║ AUS / Flem (AUS) 23rd Jun ║ R1 1400m 2yo ║ 2018-06-23 ║ 1 ║ 0 ║ NULL ║ 3 ║ 19.39 ║ NULL ║ 9 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144506971 ║ AUS / Flem (AUS) 9th Jun ║ R9 1000m 3yo ║ 2018-06-09 ║ 9 ║ 0 ║ NULL ║ 1 ║ 7.97 ║ NULL ║ 13 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144506969 ║ AUS / Flem (AUS) 9th Jun ║ R8 2000m 3yo ║ 2018-06-09 ║ 8 ║ 0 ║ NULL ║ 3 ║ 20.10 ║ NULL ║ 12 ║╠═══════════╬═══════════════════════════╬═════════════════╬════════════╬═════════════╬════════╬══════╬════════╬═══════╬═══════╬════════════╣║ 144506967 ║ AUS / Flem (AUS) 9th Jun ║ R7 1400m Hcap ║ 2018-06-09 ║ 7 ║ 0 ║ NULL ║ 1 ║ 5.71 ║ NULL ║ 14 ║╚═══════════╩═══════════════════════════╩═════════════════╩════════════╩═════════════╩════════╩══════╩════════╩═══════╩═══════╩════════════╝The table is ordered by date/race number and event_id. this should be the final order of the table.
What I’m needing is to sum the count of every row in count2 within the range between the count1 items.
It might be easier to understand with graphic (click to enlarge).
So basically what I need to do is to summarize what is on the blue boxes and then subtract it to the not null row from lead2 and add into countlead2. I can even handled it with temporal table or create a new column where to insert, so it can be cleaner and calculated later. but I don’t know how to summarize the count of the blue box.
I’ve tried with combinations of grouping sets, and different combinations of sum over partitions. but I can’t seem to be able to do what I need. I think partition by is the way to go.
So far I’ve tried with the 1 following and unbounded following to try to get the data I want, but it seems to go out of bounds. Is there a way to get the bound to be a variable? or to select the last following as the first of the next? And it seems that the preceding/following is not working with the partition I need.
Advertisement
Answer
One way is to do it in multiple stages, rather than trying to do it all in a single SELECT statement.
WITH base AS( -- your current query goes in here), base_grouped AS( -- First, create a group identifier, but in the -opposite- direction -- (This will group the rows where you want to sum [count2]) SELECT *, SUM(count1) OVER (ORDER BY [date], [race_number], event_id) AS group_id FROM base), base_grouped_summed AS( -- Then do the sum on [count2] within those groups SELECT *, SUM([count2]) OVER (PARTITION BY group_id) AS sum_count2 FROM base_grouped)-- Now deduct it from [lead2] (only does anything when [lead2] is not null)SELECT *, lead2 - LAG(sum_count2) OVER (ORDER BY group_id) AS new_valueFROM base_grouped_summedIf you know better ways to identify which rows to SUM(), you may be able to shorten this. I just went with the most obvious rules based on your sample data.
EDIT:
To demonstrate what Common Table Expressions are, this is what the above code would look like with old-school nested sub-queries…
-- Now deduct it from [lead2] (only does anything when [lead2] is not null)SELECT *, lead2 - LAG(sum_count2) OVER (ORDER BY group_id) AS new_valueFROM( -- Then do the sum on [count2] within those groups SELECT *, SUM([count2]) OVER (PARTITION BY group_id) AS sum_count2 FROM ( -- First, create a group identifier, but in the -opposite- direction -- (This will group the rows where you want to sum [count2]) SELECT *, SUM(count1) OVER (ORDER BY [date], [race_number], event_id) AS group_id FROM ( -- your current query goes here ) AS base ) AS base_grouped) AS base_grouped_summedMost people, once they get used to CTEs, find them much easier to read, modify and debug.