QUERY ISSUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

QUERY ISSUE

Author

Topic

jason_moutia
Starting Member

3 Posts

Posted - 2010-05-22 : 06:15:08

I have the following tables:

EVENT TABLE
TourID EventDate ExpertID GiftID Price Meal
B1 18/02/2010 1 G01 $85.00 No
B1 18/02/2010 1 G03 $85.00 No
B1 18/02/2010 1 G05 $85.00 No
B1 18/02/2010 1 G07 $85.00 No
R2 18/02/2010 12 G01 $125.00 No
R2 18/02/2010 12 G03 $125.00 No
R2 18/02/2010 12 G07 $125.00 No
R2 18/02/2010 12 G09 $125.00 No
R2 25/02/2010 1 G02 $170.00 Yes
R2 25/02/2010 1 G05 $170.00 Yes
R2 25/02/2010 1 G08 $170.00 Yes
S1 3/06/2010 5 G02 $130.00 No
S1 3/06/2010 5 G04 $130.00 No
S1 3/06/2010 5 G09 $130.00 No
W1 3/06/2010 5 G01 $105.00 No
W1 3/06/2010 5 G04 $105.00 No
W1 3/06/2010 5 G06 $105.00 No

BOOKING_CUST TABLE
CustID TourID EventDate Paid
C078 R2 18/02/2010 Yes
C078 W1 3/06/2010 No
C123 B1 18/02/2010 Yes
C123 W1 3/06/2010 No
C178 B1 18/02/2010 Yes
C191 B1 18/02/2010 Yes
C191 R2 25/02/2010 Yes
C218 B1 18/02/2010 Yes
C218 R2 25/02/2010 Yes
C225 R2 18/02/2010 Yes
C267 R2 18/02/2010 Yes
C334 B1 18/02/2010 Yes
C367 B1 18/02/2010 Yes
C555 B1 18/02/2010 Yes

I need to list all number of people participating in each tour. Include event where no people have booked yet. In Date / Tour Code Sequence. The result should be:

TourCode EventDate Count
B1 18/02/2010 7
R2 18/02/2010 3
R2 25/02/2010 2
S1 03/06/2010 0
W1 03/06/2010 2

My statement is as follow:
SELECT EVENT.TourID, EVENT.EventDate, count(BOOKING_CUST.TourID) AS [Count]
FROM EVENT
LEFT JOIN BOOKING_CUST
ON EVENT.TourID = BOOKING_CUST.TourID
GROUP BY EVENT.TourID, EVENT.EventDate;

AND I HAVE THE FOLLOWING RESULT:
TourID EventDate Count
B1 18/02/2010 28
R2 18/02/2010 20
R2 25/02/2010 15
S1 3/06/2010 0
W1 3/06/2010 6

My count is not correct. Can anyone help me please

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-22 : 06:54:28

You should get distinct tour id and event date befor joining like this:


SELECT E.TourID, E.EventDate, COUNT(BC.TourID) AS [Count]
  FROM (SELECT E.TourID, E.EventDate
          FROM EVENT AS E
         GROUP BY E.TourID, E.EventDate) AS E
       LEFT OUTER JOIN
       BOOKING_CUST AS BC
       ON E.TourID = BC.TourID
 GROUP BY E.TourID, E.EventDate;

jason_moutia
Starting Member

3 Posts

Posted - 2010-05-22 : 07:49:49

Thanks it work.

I have another query issue. Pretty much the same as above. There's an additional table

TABLE EXPERT

ExpertID E_name E_surname E_phone
1 Geoff Millar 92141111
5 Sue Davies 92142222
8 Hillary Cork 92144444
12 Penny Folds 92143333

I need to list Tour Code, Event Date, Expert name and Total Payment made for each tour event.The list must be in Tour Code / Event Date sequence.

The result should be:

TourCode EventDate ExpertFirstName ExpertSurname Total
B1 18/02/2010 Geoff Millar 510
R2 18/02/2010 Penny Folds 375
R2 25/02/2010 Geoff Millar 340

My statement is as follow:
SELECT BOOKING_CUST.TourID, BOOKING_CUST.EventDate, EXPERT.E_name, EXPERT.E_surname, (count(BOOKING_CUST.TourID) * EVENT.price) AS [Total]
FROM (BOOKING_CUST LEFT OUTER JOIN EVENT ON BOOKING_CUST.TourID = EVENT.TourID)
LEFT OUTER JOIN EXPERT
ON EVENT.ExpertID = EXPERT.ExpertID
WHERE EVENT.EventDate = BOOKING_CUST.EventDate
AND PAID = YES
GROUP BY BOOKING_CUST.TourID, BOOKING_CUST.EventDate, EXPERT.E_name, EXPERT.E_surname, EVENT.price;

AND I HAVE THE FOLLOWING RESULT:
TourID EventDate E_name E_surname Total
B1 18/02/2010 Geoff Millar $2,380.00
R2 18/02/2010 Penny Folds $1,500.00
R2 25/02/2010 Geoff Millar $1,020.00

I tried to DISTINCT the TourID and EventDate but I got it wrong. Probably made a mistake. Can you please help me?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources