Calculate column based of count of other column in select with case

user_2572899 0
Viewed 115

I have the following set of data:

EVENT_ID    MENU_HINT                   EVENT_NAME      SELECTION_ID EVENT_DT   WIN_LOSE    BSP
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19276642    16-Jun-18   0           46.91005891
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19052159    16-Jun-18   0           9.2
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19276641    16-Jun-18   0           11
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19276644    16-Jun-18   0           7.698731493
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19276646    16-Jun-18   0           421.7295978
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19276645    16-Jun-18   0           89.22199353
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19276647    16-Jun-18   0           150
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19276643    16-Jun-18   0           48.90986662
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19276650    16-Jun-18   1           3.466233972
144705336   AUS / KemG (AUS) 16th Jun   R3 1000m 2yo    19276640    16-Jun-18   0           2.88

For this, I need to calculate some extra columns, for example:

CountWinnerNotPrice89to1000Runners7to12

and 

SumWinnerNotPrice89to1000Runners7to12

The logic for CountWinnerNotPrice89to1000Runners7to12 is: the count of the event_id with the following conditions:

  • the BSP must be between 89 and 1000 (3 rows cover this)
  • the "win_lose" must be 0 (as it's "no price")
  • the total number of event_id's must be between 7 and 12 (the number of runners)

So the result of this column should be 3, and for the SumWinnerNotPrice89to1000Runners7to12 the same conditions apply, but I have to return the SUM of the BSP columns, so the result is 660.95

This is one example, with this data, I have several columns, one more example, if I have

CountWinnerNotPrice7to15Runners7to12
SumWinnerNotPrice7to15Runners7to12

then the results, as BSP will be between 7 and 15, are 

CountWinnerNotPrice7to15Runners7to12 = 3

SumWinnerNotPrice7to15Runners7to12 = 27.89

I have to do A LOT of this type of columns, but I'm not understanding a proper way to do this type of conditions.

I tried with CASE in the select, but I can't get it to work when I have to take in account the count of runners. I also tried with scalar functions (it takes ages to return data)

I think the logic should be done without having to do recursive searches, (no CTE I think) as I have around 900 columns to add and this will certainly kill it.

How can the logic for doing it with a case could be? I think I'm missing something, as I tried similar to:

SELECT COUNT(td.EVENT_NAME), 
       td.SELECTION_NAME, 
       SUM(CASE
               WHEN(td.BSP >= 89
                    AND td.BSP < 1000)
                   AND td.WIN_LOSE = 0
           AND COUNT(td.EVENT_NAME) > 7 --this doesn't work
           AND COUNT(td.EVENT_NAME) <= 12 --this doesn't work
               THEN td.BSP
           END) AS SumWinnerRunners
FROM tblData td
WHERE td.EVENT_ID = 144705336
GROUP BY td.SELECTION_NAME, 
         td.BSP;

I'm thinking I'm not that far, but I can't take it to the finish line here.

user-defined-functions tsql sql-server sql
1 Answers

You can use apply 

select 
  tblcnt.cnt
  ,td.selection_name
  ,case 
     when cnt between 7 and 12
     then SumWinnerNotPrice89to1000Runners7to12
     else 0
   end as SumWinnerNotPrice89to1000Runners7to12
  ,case 
     when cnt between 7 and 12
     then CountWinnerNotPrice89to1000Runners7to12
     else 0
   end as CountWinnerNotPrice89to1000Runners7to12
from tblData td
outer apply (
  select 
    count(*)
    ,sum(
      case 
        when tdi.BSP >= 89 
        and  tdi.BSP < 1000
        and  tdi.WIN_LOSE = 0
      then tdi.BSP
      else 0
    )
    ,sum(
      case 
        when tdi.BSP >= 89 
        and  tdi.BSP < 1000
        and  tdi.WIN_LOSE = 0
      then 1
      else 0
    )
  from tblData tdi
  where tdi.event_id = td.event_id
) tblcnt(cnt,SumWinnerNotPrice89to1000Runners7to12,CountWinnerNotPrice89to1000Runners7to12)

You can add in the outer apply as many cases as you want.

user_6660880
Related