SQL Query

[Basher]

Ok, I have something i just can't get my head round, spent ages trying to get it to work last night but to no avail, so here goes!

I have a query, I wish to get the query to display the customer firstname and surname and the number of videos they have hired, the tables look like this (i have only included the fields i think we need):

Customer---

firstname | surname | memberid

rental---

memberid | rentalnumber

so i was thinking count the number of times the customers memberid appears in *** table and bobs your uncle, well i got it to display the member id along side how many times it occurs:

select memberid, count(memberid)
from rentalhead1
group by memberid

but when ever i try and link/relate it to the customer table, it does not work as i can not use the surname/firstname columsn where a group by is in there!

any help would be great,

cheers

[Iain]

SELECT c.firstname, c.surname, COUNT(c.memberid) AS num_rentals
FROM customer AS c, rental AS r
WHERE r.memberid=c.memberid
GROUP BY c.memberid

should do the trick I think

[Spunkey]

I assume your version is for MySQL Az as i couldnt get it to work on SQL Server - it wasnt happy without an inner join.

So if you use MS SQL, heres what you'd need.

SELECT Customer.FirstName, Customer.Surname, COUNT(Customer.MemberID) AS [Num_Rentals]
FROM Customer
INNER JOIN Rental ON Customer.MemberID = Rental.MemberID
GROUP BY Customer.MemberID
ORDER BY [Num_Rentals] DESC, Customer.Firstname, Customer.Surname

[malfunction]

IIRC MS SQL requires you to put ALL of the other columns in the group by clause if you don't use the join syntax

[Iain]

Yes, mine was MySQL, he hasn't stated what it was so that might be the first answer to help him

[Basher]

hey, cheers guys, i'm using Oracle. I got this to work ...

SELECT C.memberid, C.SurName, C.FirstName, Count(R.memberid) TotalHired
FROM member1 C
INNER JOIN rentalhead1 R ON R.MemberId = C.MemberId
GROUP BY C.MemberId, C.SurName, C.FirstName

thanks for your help