mysql data retrieval questions

rental table:开发者_如何学编程

CREATE TABLE RENTAL
(
TransactionNo int NOT NULL AUTO_INCREMENT,
MemberID int NOT NULL, 
ItemNo char(3) NOT NULL,
RentalEmployeeID varchar(30),
ReturnEmployeeID varchar(30), 
Checkout_date DATE, 
Checkin_date DATE,
Return_date DATE,
ItemQuantity int(11) NOT NULL,
TotalPrice DOUBLE(10,2) NOT NULL,
ItemFee DOUBLE(10,2),
PRIMARY KEY(TransactionNo),
FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
FOREIGN KEY(ItemNo) REFERENCES Item(Itemno),
FOREIGN KEY(RENTALEMPLOYEEID) REFERENCES Employee(EmployeeID),
FOREIGN KEY(RETURNEMPLOYEEID) REFERENCES Employee(EmployeeID)
)

I am trying to retrieve the query for all of the customers that have purchased at least 2 items on the same day, however; i am still unable to accomplish it. Is a nested clause necessary for this?

my statement:

SELECT m.MemberID, r.`checkout_date`, SUM(r.itemquantity)
FROM RENTAL AS r, MEMBER AS m
WHERE r.MemberID = m.MemberID
GROUP BY  m.MemberID, r.`checkout_date`
HAVING SUM (r.itemquantity) > 1

This new statement will give me what i want, however; because the rental information is not required(NULL) it sums up all of the return items as well and not the just rental.

---

You dont need a nested clause - 'GROUP BY' does the trick.

SELECT m.MemberID, r.`checkout_date`, COUNT(r.itemno)
FROM RENTAL AS r, MEMBER AS m
WHERE r.MemberID = m.MemberID
GROUP BY r.MemberID, `r.checkout_date`

---

              group by memberid, checkoutdate having count(transactionid) > 1