INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Summing Group Summaries

Summing Group Summaries

(OP)
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:

BookingRef	Voucher#	No_of_Passengers
AAA		0001		2
AAA		0002		2
AAA		0003		2
BBB		0004		2
BBB		0005		1
CCC		0006		4
 
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.

RE: Summing Group Summaries

Hi

Try this

CODE --> Server

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close