I get the followng error:
Msg 8120, Level 16, State 1, Line 93
Column 'buyer.buyerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
for this query:
SELECT buyerID, buyerName FROM buyer AS a
INNER JOIN buyerItem AS b ON buyer_ID = buyerID
GROUP BY buyer_ID ORDER BY COUNT(item_ID) DESC SET ROWCOUNT 1;
WHAT IM TRYING TO DO
The most active buyer (the one who has bought the most number of items)
MY tables:
create table buyer
(
buyerID INT IDENTITY(1000,1) NOT NULL,
buyerName varchar(20) NOT NULL,
buyerUsername varchar(20) UNIQUE NOT NULL,
buyerPassowrd varchar(32) NOT NULL,
contact_ID int,
creditCardID int,
primary key(buyerID),
Foreign Key(contact_ID) references contactInfo(contactID),
Foreign Key(creditCardID) references creditCardInfo(credit_CardID));
create table buyerItem
(
buyer_ID int NOT NULL,
item_ID int NOT NULL,
PRIMARY KEY(buyer_ID,item_ID),
FOREIGN KEY (buyer_ID) REFERENCES buyer(buyerID),
FOREIGN KEY (item_ID) REFERENCES item(itemID));
LET ME KNOW IF YOU NEED MORE INFORMATION