Hi everybody, I have following problem with my SQL query.
I have two tables, 1:N. The first table is called Auction, where each row represents one concrete auction. Second table is called AuctionDeposit and can contains N rows for every auction. These rows represents user bids for product. My SQL query
SELECT AUCT.auctionID, AUCT.userID, AUCT.productID,
MAX(AUCTDEP.actualPrice), AUCT.startingPrice, AUCT.minBidPrice,
AUCT.startDate, AUCT.endDate
FROM dbo.Auction AUCT INNER JOIN dbo.AuctionDeposit AUCTDEP
ON AUCT.auctionID = AUCTDEP.auctionID
WHERE AUCT.auctionID = @auctionID
gives me this error:
Column 'dbo.Auction.auctionID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I googled that I have to add GROUP BY clausule, but than I have to add GROUP BY AUCT.auctionID, AUCT.userID, AUCT.productID, each attribute called in SQL query. And this can make a problem, because I can have more than one auction for each product (productID).
Is there some more effective sql query to achieve the solution, instead of adding GROUP BY clausule with every attribute I want?
You can replace the MAX() column with a Sub-select that gets the value you want.
SELECT AUCT.auctionID, AUCT.userID, AUCT.productID,
(SELECT MAX(actualPrice) FROM SomeTable WHERE SomeCondition=true) AS MaxPrice,
AUCT.startingPrice, AUCT.minBidPrice,
AUCT.startDate, AUCT.endDate
FROM .....
shony83
Member
12 Points
12 Posts
SQL query problem - columns are invalid in the select list because it is not contained in either ...
Dec 21, 2012 01:55 PM|LINK
Hi everybody, I have following problem with my SQL query.
I have two tables, 1:N. The first table is called Auction, where each row represents one concrete auction. Second table is called AuctionDeposit and can contains N rows for every auction. These rows represents user bids for product. My SQL query
gives me this error:
I googled that I have to add GROUP BY clausule, but than I have to add GROUP BY AUCT.auctionID, AUCT.userID, AUCT.productID, each attribute called in SQL query. And this can make a problem, because I can have more than one auction for each product (productID).
Is there some more effective sql query to achieve the solution, instead of adding GROUP BY clausule with every attribute I want?
Thanks for reply
Shony
spapim
Contributor
2446 Points
368 Posts
Re: SQL query problem - columns are invalid in the select list because it is not contained in eit...
Dec 21, 2012 02:58 PM|LINK
Hi,
Can you post a sample of data from tables and its expected result?
www.imobiliariasemsuzano.com.br
TabAlleman
All-Star
15571 Points
2700 Posts
Re: SQL query problem - columns are invalid in the select list because it is not contained in eit...
Dec 21, 2012 02:59 PM|LINK
You can replace the MAX() column with a Sub-select that gets the value you want.
SELECT AUCT.auctionID, AUCT.userID, AUCT.productID, (SELECT MAX(actualPrice) FROM SomeTable WHERE SomeCondition=true) AS MaxPrice, AUCT.startingPrice, AUCT.minBidPrice, AUCT.startDate, AUCT.endDate FROM .....oned_gk
All-Star
31373 Points
6412 Posts
Re: SQL query problem - columns are invalid in the select list because it is not contained in eit...
Dec 21, 2012 11:28 PM|LINK
SELECT AUCT.auctionID, AUCT.userID, AUCT.productID, MAX(AUCTDEP.actualPrice), AUCT.startingPrice, AUCT.minBidPrice, AUCT.startDate, AUCT.endDate FROM dbo.Auction AUCT INNER JOIN dbo.AuctionDeposit AUCTDEP ON AUCT.auctionID = AUCTDEP.auctionID WHERE AUCT.auctionID = @auctionID GROUP BY AUCT.auctionID, AUCT.userID, AUCT.productID, AUCT.startingPrice, AUCT.minBidPrice, AUCT.startDate, AUCT.endDatenikunjnandan...
Participant
882 Points
223 Posts
Re: SQL query problem - columns are invalid in the select list because it is not contained in eit...
Dec 23, 2012 06:24 AM|LINK
Hiii,
You should have to specify all selected field in group by.
GROUP BY AUCT.auctionID, AUCT.userID, AUCT.productID, AUCT.startingPrice, AUCT.minBidPrice,AUCT.startDate, AUCT.endDateSql server is not allow you to select field which is not specify in group by. MY sql will allow this way.
Nikunj Nandaniya
My Blog
shony83
Member
12 Points
12 Posts
Re: SQL query problem - columns are invalid in the select list because it is not contained in eit...
Dec 23, 2012 03:06 PM|LINK
Hi TabAlleman, your post is the solution for me, thank you very much
And I'm sorry for my late answer :-)