Help with a sql query

I have 2 tables:

Customer(CustomerId, Name, Surname)
CustomerOrders(OrderId, Total)

I want to display the following information in a table:

Name | Surname | Average Spend |
Joe | Bloggs | $68.96 |
Simon | Smith | $128.34 |


I need one query that will pull out these values, but also do the average spend depending on how many orders a customer has. I need it in one query so I can sort each column in the final table.

Hope someone can help, cheers.

 

Select Customer.Name, Customer.Surname, (select avg(CustomerOrders.Total) from CustomerOrders where CustomerOrders.customerID = Customer.CustomerID) From Customer

In order for this to work, you need to store the customerID for the in the order table. The way you have it listed here, there is no way to link the order to the customer.

You may need to add some code to prevent a divide by 0 (or it may automatically null itself, run it and see).

 

With your current db structure you also need to include the table that links orderID to customerID

 

I don't think that should be a separate table. That would require joins which would take more resources. I think the customerID should be a column in the order table.

 

I agree with bluegrass that the customerID should be a column (foreign key) in the CustomerOrders table.

But I would rather use a join and a group by than a sub query (don't know if that is actually more efficient or not.. )

Then the query would be something like:

SELECT Customer.Name, Customer.Surname, AVG(CustomerOrders.Total)
FROM Customer
INNER JOIN CustomerOrders
ON Customer.CustomerId= CustomerOrders.CustomerId
GROUP BY Customer.CustomerId, Customer.Name, Customer.Surname

Code (markup):

 

Thanks for the replies Guys,

I left out a field from the customerOrders table, it does indeed have a CustomerID field to cross reference.

Im trying you suggestions now and will repost back!

 

I think there is an error in the above SQL Statement.

(select avg(CustomerOrders.Total) from CustomerOrders where CustomerOrders.customerID = Customer.CustomerID)

Code (markup):

should be replaced with:

(select avg(CustomerOrders.Total) from CustomerOrders, [COLOR="Red"]Customer [/COLOR]where CustomerOrders.customerID = Customer.CustomerID)

Code (markup):

cheers,
jay

 

I think the SQL statement should be without Customer.CustomerId in the GROUP BY clause. Also, I am not sure the SQL statement below will work. I will be glad to get feedback.

SELECT Customer.Name, Customer.Surname, AVG(CustomerOrders.Total)
FROM Customer
INNER JOIN CustomerOrders
ON Customer.CustomerId= CustomerOrders.CustomerId
GROUP BY Customer.Name, Customer.Surname

Code (markup):

cheers,
jay

 

Another Solution is:

SELECT Name, Surname, q.Average "Average Spend"
FROM Customer c, (SELECT CustomerID, AVG(Total) "Average" FROM CustomerOrders GROUP BY CustomerID) q
WHERE c.CustomerID = q.CustomerID


Yet Another is:

SELECT Name, Surname, q.Average "Average Spend"
FROM Customer c
INNER JOIN (SELECT CustomerID, AVG(Total) "Average" FROM CustomerOrders GROUP BY CustomerID) q
ON c.CustomerID = q.CustomerID

cheers,
jay

 

Thanks for your input jayasimha, the query below seems to work, but instead of outputting the average the total spend instead of the average spend comes out...weird??

SQL="SELECT Customer.firstname, Customer.Surname, AVG(CustomerOrders.Total) FROM Customer INNER JOIN CustomerOrders ON Customer.CustomerId= CustomerOrders.CustomerId GROUP BY Customer.firstname, Customer.Surname"

Code (markup):

*EDIT Sorry my mistake, this works a treat!! I needed to add more orders! Cheers

 

Hi Tomred,

If my post was of some help to you, I request you to give me a reputation point. Thanks.

cheers,
jay

 

No error. I intentionally did not want to join the tables. I can't be sure about MySQL, but in SQL server, subqueries are usually faster than a join. If that is the case for MySQL, then you should only do a join if you are returning more than one column of data from the joined table. In this case, one column of data so a subquery rather than a join.

P.S. They don't like it when you ask for rep around here. You either get it or don't.

 

Back again guys....

Ive just realised the SQL query Jay provided only pulls out the customers that have made an order...

Can i pull out all the customers, even if they have not made an order?

 

Just replace the INNER join with an OUTER join. I think it is a LEFT OUTER JOIN.

 

Hi Bluegrass special,

Thanks for your comments on SQL server. But I can not verify because I do not use that product.

Also thanks for the feedback on rep. points. I am new here and am still learning the culture of this place. Sorry if I sounded like a beggar.

cheers,
jay

 

No problem, I personally don't care. But I thought I'd save you trouble in the future.