1-I want to execute this query : my item table looks like that: seller table: cr
ID: 3825221 • Letter: 1
Question
1-I want to execute this query :
my item table looks like that:
seller table:
create table seller
(
sellerID INT IDENTITY(1000,1) NOT NULL,
sellerName varchar(20) NOT NULL,
sellerUserName varchar(20) UNIQUE,
sellerPassword varchar(32) NOT NULL,
item_ID int NOT NULL,
contact_ID int NOT NULL,
creditCardID int NOT NULL,
primary key(sellerID),
Foreign Key(contact_ID) references contactInfo(contactID),
Foreign Key(creditCardID) references creditCardInfo(credit_CardID));
buyerItem table
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));
SO the problem is that I have Column 'item.itemID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. i tried to add a group by clause at the end group by c.itemID, c.itemName, but its the same error? dont really know where the problem comes from??
2- I also have this query
I want to add itemQty along with the ownerID and sellerName from item table stated above, what would be the best way to achieve that?
Explanation / Answer
--Inner query grouped by item_ID,which resolves error
--Modified part : Group by b.item_ID(In inner query)
SELECT c.itemID, c.itemName
FROM item AS c
JOIN
(
SELECT b.itemID as 'itemid', MAX(b.item_initialPrice) AS 'MaxPrice'
FROM buyeritem AS a
INNER JOIN item AS b
ON a.item_ID = b.itemID
Group by b.itemID
) as d
ON c.itemID = d.itemid
group by c.itemID, c.itemName;
--I am hoping you want sum of itemQty, grouped by ownerID and selllername...
--Below Query serves your purpose
--Modified part : Sum(a.ItemQty) as SumItemQty
SELECT a.ownerID, b.sellerName ,Sum(a.ItemQty) as SumItemQty
FROM item AS a
INNER JOIN seller AS b
ON a.ownerID = b.sellerID
GROUP BY a.ownerID,b.sellerName
ORDER BY COUNT(a.itemID) DESC;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.