Summing Group Summaries

[pmax9999]

I have many years experience with Crystal Reports but am now working with MS SQL Report Builder (v3.0) and to be honest I am struggling with it. I am currently restricted to working with the data models previously set up and do not have access to be able to write the SQL Query that would so easilly return the result I need.

I work for a travel company and have a situation where I am trying to count the number of passengers, but it is complicated in that an indiviual booking will have multiple service components (vouchers), each of which will have the number of passengers. In other words, 2 passengers appearing on many vouchers within the same BookingRef will be the same two passengers and should therefore only be counted once per booking.

Sample data as follows:

[pre]
BookingRef Voucher# No_of_Passengers
AAA 0001 2
AAA 0002 2
AAA 0003 2
BBB 0004 2
BBB 0005 1
CCC 0006 4
[/pre]
The number of passengers can vary between vouchers within the same bookingRef. For an individual BookgRef the Max(No_of_Passengers) provides the correct result, but I have not been able to come up with a way to get a Sum of those BookingRef group results. In the example above the correct result is 8 passengers (2 for the 1st BookingRef, 2 for the second and 4 for the 3rd).

If I was working with Crystal I would have solved this problem hours ago, but just can't find a way to do it Booking Builder.

Any suggestions gratefully accepted.

Regards.

Pete.

 

[Motlatjo]

Hi

Try this

WITH cte(BookingRef, Voucher, No_of_Passengers)
AS
(
SELECT 'AAA'	BookingRef, 	'0001' Voucher,	2 No_of_Passengers
UNION ALL
SELECT 'AAA',		'0002',		2
UNION ALL
SELECT 'AAA',		'0003',		2
UNION ALL
SELECT 'BBB',		'0004',		2
UNION ALL
SELECT 'BBB',		'0005',		1
UNION ALL
SELECT 'CCC',		'0006',		4
)
SELECT BookingRef, 
       Voucher, 
       No_of_Passengers
FROM(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY BookingRef ORDER BY Voucher) ranks
	FROM cte ) t
WHERE t.ranks = 1