sql server - How to calculate a daily running total from an aliased column -

i'm writing query return table of booking data event. i've used sum aggregate group number of daily bookings. i'd create column cumulative running total.

i can't make query work because (i'm presuming) doesn't aliased column title being included in aggregate function.

please can advise me on best approach make work?

select     'recruitment event' event,     cast(ep.creationdate date) 'date of booking',     sum(case         when ep.creationdate not null 1         else 0     end) 'total bookings',     (select         sum('total bookings')     eventplace ep     cast(ep.creationdate date) <= cast(ep.creationdate date))     'running total' eventplace ep left join eventmodule em     on em.eventmoduleno = ep.eventmoduleno em.eventmoduleno = '11111111-abcd-1234-1234-1010101010' group cast(ep.creationdate date) order cast(ep.creationdate date) desc 

try this

select       [event]             = 'recruitment event' ,     [date of booking]   = cast(ep.creationdate date),     [total bookings]    = count(ep.creationdate),     [running total]     = sum(count(ep.creationdate)) on (order cast(ep.creationdate date) desc)         eventplace ep        exists (select                  *                               eventmodule em                               em.eventmoduleno = ep.eventmoduleno                  , em.eventmoduleno = '11111111-abcd-1234-1234-1010101010') group      cast(ep.creationdate date) order      cast(ep.creationdate date) desc 

count(ep.creationdate) ignore null values same result sum(case)

you dont have use exists, should change left join join since you're using em.eventmoduleno in where statement

sum(count(ep.creationdate)) on () give running total of count. use order by in over determine order of sum. since you're ordering cast(ep.creationdate date) desc can use in over()