2 tables . One always have a value but the second one is not always has a value.
So this will work only if vwDWTickets has a value that is also in bookingFee:
with tickets(admissions,Transnumber,value) as ( select sum(T.admissions) as admissions,T.TransNumber,sum(GrossValue) from vwDWTickets T where T.TransNumber = 10550901 group by T.TransNumber) select tickets.admissions,tickets.Transnumber,tickets.value + sum(tblDWBookingFee.GrossValue) from tickets inner join tblDWBookingFee on tickets.Transnumber = tblDWBookingFee.Transnumber group by tickets.admissions,tickets.Transnumber,tickets.value,tblDWBookingFee.GrossValue
I did this with CTE becuase tblDWBookingFee can also have more than one row so with a simple join it will not give me the correct admissions count.
So if transaction 10550901 has booking fees then no problem, if it does not then I get no rows (logical because the inner join has no joined values)
How can I ignore the join an get all the values from "tickets" (aka vwDWTickets ) regardless if tblDWBookingFee joins with values or not?