1

I have a basic table named customer:

CustId Name
AB1    George Arkin
AB2    Indiana James
AB3    Michael Anjelo

and another table named booking:

CustId FlightId Price
AB1    FL134    43.00 
AB1    FL122    21.00
AB1    FL134    49.00
AB1    FL122    48.00  
AB2    FL291    40.00  
AB2    FL291    29.00  
AB2    FL293    22.00  
AB2    FL862    56.00  
AB2    FL862    12.00  
AB2    FL293    99.00  
AB3    FL900    100.00

Now what I'd like to do is join both tables. I would then like to calculate how many flights a single person has booked (displaying all persons) and I would also like for each person the have a total price next to it adding up the total of each price they have paid. So far I came up with this:

SELECT C.CustId, C.Name, COUNT(DISTINCT B.FlightId) AS "NumberOfTicketsPurchased"
,      SUM(DISTINCT B.Price) AS "TotalPrice"
FROM customer C, booking B

But I only get one result and the total price and count is not accurate.

P.S this is an example table for something I am practising in my own time to prepare for Data Management next Semester.

Improve this question

5 Answers 5

Reset to default
1

You will required GROUP BY for using Aggregate functions like Count and SUM. Did you try this ?

SELECT C.CustId, C.CName, Count(B.FlightId) , SUM(B.Price)
FROM customer C
LEFT JOIN booking b ON b.CustId = a.CustId 
GROUP BY  C.CustId

EDIT 

SELECT C.CustId, C.CName, Count(B.FlightId) , SUM(B.Price) as 'price'
FROM customer C
INNER JOIN booking b ON b.CustId = a.CustId 
GROUP BY  C.CustId
HAVING price > 75
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

This result displayed 0's in the count colomn but some did show up as 2 or 3 flights. The SUM colomn showed up different prices for only the rows with a number of flights rather than a 0. The rest of the prices are null. I wonder if it's possible that this is just a data area or a code error.
@MichaelAlgeo What is your expected answer ?
My expected answer was that it display the flights and not zeros. I would also not like NULL values but I can see now that the query is correct but the data I think is incorrect.
I think you will require to alter your table and set default value to 0 instead of null. I believe this will solve the issue
Hmmm. I've tried that, still nothing. I've even tried removing not null and still get the same result of nulls. Very Strange. I would also like to know where to position an IF statement if I wanted to only display people with total price of over 75.
|
1

Yet another one SQL Fiddle:

SELECT C.CustId, C.Name,
       COUNT(B.FlightId) AS "NumberOfTicketsPurchased",
       coalesce(SUM(B.Price), 0) AS "TotalPrice"
FROM customer C
left join booking B on c.custid = b.custid
group by c.custid, c.name;
Improve this answer

6 Comments

This answer is the same result as 2 people. I do believe that it may be a data error possibly. Only 4 people have flights according to this with each different numbers. And only these 4 people have total prices. This is very strange. I might try and re-create the database from Scratch.
And thank you for SQL Fiddle. I can see now that the code is correct but the data is incorrect. Thank you.
@MichaelAlgeo Which code is correct, the first or the second select?
@MichaelAlgeo See the modified answer/SQL Fiddle. Price now shows as 0 instead of null.
The second because I want it to display the count for every flight. But I still have issues with having Null's in my total price and 0's in my flights.
|
0 
SELECT C.CustId, C.Name, COUNT(B.FlightId) AS "NumberOfTicketsPurchased"
,SUM(B.Price) AS "TotalPrice"
FROM customer C join booking B
on c.CustId = b.CustId 
group by C.CustId, C.Name
Improve this answer

1 Comment

This answer was also similar to Moyeds but the only difference is that yours did not display the people with the NULL value or the 0 value.
0

First, SUM(DISTINCT B.Price) is never a good idea. If two flights have the same price, they'd only be counted once.

In your case you're only doing one join, so there's no need for distinct at all:

select  c.CustId
,       c.Name
,       count(b.FlightId) AS "NumberOfTicketsPurchased"
,       sum(b.Price) AS "TotalPrice"
from    customer c
left join
        booking b
on      b.CustId = c.CustId
group by
        c.CustId
,       c.Name
Improve this answer

1 Comment

This answer was much like Moyeds, This result displayed 0's in the count colomn but some did show up as 2 or 3 flights. The SUM colomn showed up different prices for only the rows with a number of flights rather than a 0. The rest of the prices are null. I wonder if it's possible that this is just a data area or a code error.
-1

Try

SELECT C.CustId, C.Name 
,COUNT(DISTINCT B.FlightId) AS "NumberOfTicketsPurchased", 
SUM(DISTINCT B.Price) AS "TotalPrice" 
FROM customer C, booking B 
GROUP BY C.CustId
Improve this answer

1 Comment

This particular answer showed everyone is having the same number of flights booked and the same maximum price for the tickets purchased. I would like it to display different answers for everyone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.