Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: SUM and JOIN and possibly GROUP BY - incorrect value returned by SUM

    It's me again

    So; if you read my earlier thread here, you'll know that I'm trying to build stored procedures to deal with ticketing queries, and that it's all getting a bit complicated. I have, however, made a bit of progress and now have the following working:

    Code:
    CREATE PROCEDURE [dbo].[getAvailableTickets]
    @eventId INT,
    @standId INT,
    @admissionDateId INT,
    @concessionId INT,
    @userId INT
    
    AS
    
    	DECLARE @startyear DATETIME
    	DECLARE @endyear DATETIME
    	SELECT @startyear=CONVERT(datetime, '2006/01/01')
    	SELECT @endyear=CONVERT(datetime, '2006/12/31')
    
    
    SELECT
    	[tblTickets].[id] AS ticketId,
    	[tblEvents].[id] AS eventId,
    	[tblStands].[id] AS standId,
    	[tblAdmissionDates].[id] AS admitDateId,
    	[tblEvents].[event_name],
    	[tblStands].[stand_name],
    	[tblTicketConcessions].[concession_name],
    	[tblMemberships].[membership_name],
    	[tblAdmissionDates].[admission_start_date],
    	[tblAdmissionDates].[admission_end_date],
    	[tblBookingMinQuantities].[booking_quantity] AS minBookingQuantity,
    	[tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity,
    	MIN([tblQuotas].[quota]) AS Quota,
    	SUM([tblBasket].[ticket_quantity]) AS History,
    	[tblTickets].[price],
    	[tblTickets].[availability]
    
    FROM [tblTickets]
    	LEFT JOIN [tblEvents]						ON [tblEvents].[id] = [tblTickets].[event_id]
    	LEFT JOIN [tblStands]						ON [tblStands].[id] = [tblTickets].[stand_id]
    	LEFT JOIN [tblBookingDates]					ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
    	LEFT JOIN [tblTicketConcessions]				ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
    	LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities 	ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
    	LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantities	ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
    	LEFT JOIN [tblAdmissionDates]					ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
    	LEFT JOIN [tblMemberships]					ON [tblMemberships].[id] = [tblTickets].[membership_id]	
    	LEFT JOIN [tblQuotas]			ON
    						([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND
    						([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND
    						([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND
    						([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND
    						([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND
    						([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL)
    	LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id]
    	LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]
    
    WHERE 1=1
    AND ([tblTickets].[ticket_open] = 1)
    AND (([tblEvents].[id] = @eventId OR @eventId = 0)				AND ([tblEvents].[event_open] = 1))
    AND (([tblStands].[id] = @standId OR @standId = 0)				AND ([tblStands].[stand_open] = 1))
    AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)	AND ([tblAdmissionDates].[date_open] = 1))
    AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0)
    AND ((getdate() BETWEEN [tblBookingDates].[booking_start_date] AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL))
    AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL)
    AND ([tblOrders].[user_id] = @userId OR @userId=0)
    
    GROUP BY
    [tblTickets].[id],
    [tblEvents].[id],
    [tblStands].[id],
    [tblAdmissionDates].[id],
    [tblEvents].[event_name],
    [tblStands].[stand_name],
    [tblTicketConcessions].[concession_name],
    [tblMemberships].[membership_name],
    [tblAdmissionDates].[admission_start_date],
    [tblAdmissionDates].[admission_end_date],
    [tblBookingMinQuantities].[booking_quantity],
    [tblBookingMaxQuantities].[booking_quantity],
    [tblTickets].[price],
    [tblTickets].[availability]
    GO

    Except... there's two problems with it. One is that it only returns tickets that you've already bought, the other is that it doens't work out correctly how many of those tickets you've bought in past orders.

    This is what's in tblBasket:

    Code:
    	id	ticket_id	quantity	order_id	date
    	1	5		2			3		21/03/2006
    	2	6		2			3		21/03/2006
    	3	14		4			4		21/03/2006
    	4	15		4			4		21/03/2006
    	5	5		1			4		21/03/2006
    Both the orders in there are for the same user id: "1". All the tickets in there are tied to event "2".

    When I run Exec dbo.getAvailableTickets 2,0,0,0,1, it tells me it's found 6 of ticket 5, 4 of ticket 6 and 12 of ticket 14. And I can't for the life of me figure out how it's calculating it. Any ideas?

    And how do i get it to return all tickets regardless of whether you've bought them previously or not?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Which field indicates the number found? "SUM([tblBasket].[ticket_quantity]) AS History"?

    If so, this count can get inflated if you are joining with one-to-many relationships. The way to fix it is to calculate the sum in a nested subquery and then join the resultset to your outer query.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    I think I've cracked it

    Code:
    CREATE PROCEDURE [dbo].[getAvailableTickets]
    @eventId INT,
    @standId INT,
    @admissionDateId INT,
    @concessionId INT,
    @userId INT
    
    AS
    
    SELECT
    	[tblTickets].[id] AS ticketId,
    	[tblTickets].[price],
    	[tblTickets].[availability],
    	[tTicketQuotas].[Quota],
    	[tTicketHistory].[History],
    	[tblEvents].[event_name],
    	[tblStands].[stand_name],
    	[tblAdmissionDates].[admission_start_date],
    	[tblAdmissionDates].[admission_end_date],
    	[tblTicketConcessions].[concession_name],
    	[tblMemberships].[membership_name],
    	[tblBookingMinQuantities].[booking_quantity] AS minBookingQuantity,
    	[tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity
    FROM
    
    tblTickets
    
    LEFT JOIN(
    	SELECT
    		[tblTickets].[id] AS ticketId,
    		MIN([tblQuotas].[quota]) AS Quota
    	FROM
    		[tblTickets]
    		LEFT JOIN [tblQuotas]	ON
    					([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND
    					([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND
    					([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND
    					([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND
    					([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND
    					([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL)
    	GROUP BY
    		[tblTickets].[id]
    ) AS tTicketQuotas ON [tTicketQuotas].[ticketId] = [tblTickets].[id]
    
    
    LEFT JOIN(
    	SELECT
    		[tblTickets].[id] AS ticketId,
    		SUM([tblBasket].[ticket_quantity]) AS History
    	FROM
    		[tblTickets]
    		LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id]
    		LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]
    		LEFT JOIN [tblUsers] ON [tblUsers].[id] = [tblOrders].[user_id]
    	WHERE [tblUsers].[id] = @userId OR [tblUsers].[id] IS NULL
    	GROUP BY [tblTickets].[id]
    ) AS tTicketHistory ON [tTicketHistory].[ticketId] = [tblTickets].[id]
    
    
    LEFT JOIN [tblEvents]						ON [tblEvents].[id] = [tblTickets].[event_id]
    LEFT JOIN [tblStands]						ON [tblStands].[id] = [tblTickets].[stand_id]
    LEFT JOIN [tblAdmissionDates]					ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
    LEFT JOIN [tblTicketConcessions]				ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
    LEFT JOIN [tblMemberships]					ON [tblMemberships].[id] = [tblTickets].[membership_id]
    LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities 	ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
    LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantities	ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
    LEFT JOIN [tblBookingDates]					ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
    
    
    WHERE 1=1
    AND ([tblTickets].[ticket_open] = 1)
    AND (([tblEvents].[id] = @eventId OR @eventId = 0)				AND ([tblEvents].[event_open] = 1))
    AND (([tblStands].[id] = @standId OR @standId = 0)				AND ([tblStands].[stand_open] = 1))
    AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)	AND ([tblAdmissionDates].[date_open] = 1))
    AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0)
    AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL)
    AND ((getdate() BETWEEN [tblBookingDates].[booking_start_date] AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL))
    GO

    Is that what you mean by doing it in nested subqueries?

  4. #4
    Join Date
    Jan 2002
    Posts
    189
    Right. It's gone wrong again.

    I tried to wrap another SELECT round it, to get combinations of tickets. And now it's telling me:
    "Server: Msg 207, Level 16, State 3, Line 13
    Invalid column name 'id'."

    I've commented out the line "[tblC].[id] AS combinationID," to no effect. I can't see anything else that would cause the error. What's going on?


    Code:
    DECLARE @eventId INT
    DECLARE @standId INT
    DECLARE @admissionDateId INT
    DECLARE @concessionId INT
    DECLARE @userId INT
    SET @eventId = 2 
    SET @standId = 0
    SET @admissionDateId = 0
    SET @concessionId = 0
    SET @userId = 1
    
    
    SELECT
    [tblC].[id] AS combinationID,
    [tblC].[availability] AS combinationAvailability,
    [tblC].[description] AS combinationDescription,
    [tblC].[price] AS combinationPrice,
    [tblC].[combination_open],
    [tblC].[combination_live],
    [tblT].[ticketId] AS ticketId,
    [tblT].[ticketPrice] AS ticketPrice,
    [tblT].[ticketAvailability] AS ticketAvailability,
    [tblT].[Quota],
    [tblT].[History],
    [tblT].[event_name],
    [tblT].[stand_name],
    [tblT].[admission_start_date],
    [tblT].[admission_end_date],
    [tblT].[concession_name],
    [tblT].[membership_name],
    [tblT].[minBookingQuantity] AS minBookingQuantity,
    [tblT].[maxBookingQuantity] AS maxBookingQuantity
    
    FROM(
    	SELECT
    		[tblTickets].[id] AS ticketId,
    		[tblTickets].[price] AS ticketPrice,
    		[tblTickets].[availability] AS ticketAvailability,
    		[tTicketQuotas].[Quota],
    		[tTicketHistory].[History],
    		[tblEvents].[event_name],
    		[tblStands].[stand_name],
    		[tblAdmissionDates].[admission_start_date],
    		[tblAdmissionDates].[admission_end_date],
    		[tblTicketConcessions].[concession_name],
    		[tblMemberships].[membership_name],
    		[tblBookingMinQuantities].[booking_quantity] AS minBookingQuantity,
    		[tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity
    	FROM
    	
    	tblTickets
    	
    	LEFT JOIN(
    		SELECT
    			[tblTickets].[id] AS ticketId,
    			MIN([tblQuotas].[quota]) AS Quota
    		FROM
    			[tblTickets]
    			LEFT JOIN [tblQuotas]	ON
    						([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND
    						([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND
    						([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND
    						([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND
    						([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND
    						([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL)
    		GROUP BY
    			[tblTickets].[id]
    	) AS tTicketQuotas ON [tTicketQuotas].[ticketId] = [tblTickets].[id]
    	
    	
    	LEFT JOIN(
    		SELECT
    			[tblTickets].[id] AS ticketId,
    			SUM([tblBasket].[ticket_quantity]) AS History
    		FROM
    			[tblTickets]
    			LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id]
    			LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]
    			LEFT JOIN [tblUsers] ON [tblUsers].[id] = [tblOrders].[user_id]
    		WHERE [tblUsers].[id] = @userId OR [tblUsers].[id] IS NULL
    		GROUP BY [tblTickets].[id]
    	) AS tTicketHistory ON [tTicketHistory].[ticketId] = [tblTickets].[id]
    	
    	
    	LEFT JOIN [tblEvents]						ON [tblEvents].[id] = [tblTickets].[event_id]
    	LEFT JOIN [tblStands]						ON [tblStands].[id] = [tblTickets].[stand_id]
    	LEFT JOIN [tblAdmissionDates]					ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
    	LEFT JOIN [tblTicketConcessions]				ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
    	LEFT JOIN [tblMemberships]					ON [tblMemberships].[id] = [tblTickets].[membership_id]
    	LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities 	ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
    	LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantities	ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
    	LEFT JOIN [tblBookingDates]					ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
    	
    	
    	WHERE 1=1
    	AND ([tblTickets].[ticket_open] = 1)
    	AND (([tblEvents].[id] = @eventId OR @eventId = 0)				AND ([tblEvents].[event_open] = 1))
    	AND (([tblStands].[id] = @standId OR @standId = 0)				AND ([tblStands].[stand_open] = 1))
    	AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)	AND ([tblAdmissionDates].[date_open] = 1))
    	AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0)
    	AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL)
    	AND ((getdate() BETWEEN [tblBookingDates].[booking_start_date] AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL))
    
    )as [tblT]
    JOIN [tblCombinations] as [tblC] on [tblT].[id]=[tblC].[id]

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You aliased tblC.id within your subquery:

    [tblTickets].[id] AS ticketId
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •