Error in the Sql Query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

SELECT

        LXA.AuctionID
        ,LAD.OpeningPrice
        ,AP.Description
        ,UR.Name
        ,UR.BigLeapID
        ,CONVERT(VARCHAR,LXA.Date)Date

     ,CASE WHEN LXA.IsAutoBid=1 then ISNULL(max(LXA.BidPrice),0)+LAD.BidIncrement else LXA.BidPrice End topay
  FROM  LiveXAuctionBid LXA
        INNER JOIN LiveAuctionBid LA ON LA.LiveAuctionBidID = LXA.LiveAuctionBidID
        INNER JOIN UserRegistration UR ON UR.UserRegistrationID = LA.UserID
        INNER JOIN LiveAuctionDetails LAD ON LAD.LiveAuctionDetailID = LXA.AuctionID
        INNER JOIN AuctionProduct AP ON AP.AuctionProductID = LAD.ProductID
  WHERE LAD.LiveAuctionDetailID = @LiveAuctionDetailID

  group by  LXA.AuctionID,LAD.OpeningPrice,AP.Description,UR.Name,UR.ID,Date

Error:

Column 'LiveXAuctionBid.IsAutoBid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'LiveAuctionDetails.BidIncrement' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'LiveXAuctionBid.BidPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Posted 6-Nov-14 21:38pm

Gokul Athithan

Add a Solution

Comments

Thanks7872 7-Nov-14 3:43am

And which part of the error is not clear to you?

Gokul Athithan 7-Nov-14 3:44am

yes

/\jmot 7-Nov-14 3:52am

Error occurring for this part..
CASE WHEN LXA.IsAutoBid=1 then ISNULL(max(LXA.BidPrice),0)+LAD.BidIncrement else LXA.BidPrice End topay

Gokul Athithan 7-Nov-14 3:56am

how to clear the error

Tomas Takac 7-Nov-14 3:59am

The CASE statement is your problem, it's a mess actually. Maybe you should start by trying to explain why do you need GROUP BY in the first place, then it becomes more clear.

Shweta N Mishra 7-Nov-14 3:59am

You are not grouping the columns which you have selected and hence error. What is your need, why you are not using those column in group by clause.

You should add IsAutoBid,BidIncrement in group by clause . Also why at one place you are taking Max(BidPrice) and in else it is BidPrice ?

/\jmot 7-Nov-14 4:00am

i am not sure,may help you or not..
try this..

SELECT

LXA.AuctionID
,LAD.OpeningPrice
,AP.Description
,UR.Name
,UR.BigLeapID
,CONVERT(VARCHAR,LXA.Date)Date

,CASE WHEN LXA.IsAutoBid=1 then ISNULL(max(LXA.BidPrice),0)+max(LAD.BidIncrement) else max(LXA.BidPrice) End topay
FROM LiveXAuctionBid LXA
INNER JOIN LiveAuctionBid LA ON LA.LiveAuctionBidID = LXA.LiveAuctionBidID
INNER JOIN UserRegistration UR ON UR.UserRegistrationID = LA.UserID
INNER JOIN LiveAuctionDetails LAD ON LAD.LiveAuctionDetailID = LXA.AuctionID
INNER JOIN AuctionProduct AP ON AP.AuctionProductID = LAD.ProductID
WHERE LAD.LiveAuctionDetailID = @LiveAuctionDetailID

group by LXA.IsAutoBid,LXA.AuctionID,LAD.OpeningPrice,AP.Description,UR.Name,UR.ID,Date

/\jmot 7-Nov-14 4:00am

if it doesn't work then reply me here..

Gokul Athithan 7-Nov-14 5:02am

no

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-11-06T22:05:00

When you use a GROUP BY clause, it aggregates records according to the specified groups: so if you group your customers by city, you get back "n" records, where "n" is the number of different cities your customers are based in. Within that query, you can't access individual customers except by using an aggregate function which (for example) counts them, or add them together - because the GROUP converts them to a single record.

I can't suggest what you need to do to fix you query - because I have no idea what your data looks like, or what result you expect to get - but you can't access the bid status, bid price or increment within the GROUP. Most likely, you need to use a sub query of some form instead of a group, but as I say, I can;t see your data.

Go back to the drawing board, and work out exactly what you are trying to return - build a "dummy data set" in Excel or on paper - and then look at how you extract that data. I think you are heading down the wrong road at the moment.

Kan07 · Answer 2 · 2014-11-06T22:26:00

Solution 2

'having ' is used with group by function ....and also use aggregate function with fields except the group by fields like LXA.AuctionID,LAD.OpeningPrice,AP.Description,UR.Name,UR.ID,Date

Posted 6-Nov-14 22:26pm

Kan07

Corporal Agarn · Answer 3 · 2014-11-07T04:27:00

Look ate this:

SQL

WITH cte AS (
SELECT 
    LXA.AuctionID
    , ISNULL(MAX(LXA.BidPrice), 0) AS MaxBidPrice
FROM LiveXAuctionBid LXA 
GROUP BY LXA.AuctionID
)
SELECT
    LXA.AuctionID
    ,LAD.OpeningPrice
    ,AP.Description
    ,UR.Name
    ,UR.BigLeapID
    ,CONVERT(VARCHAR,LXA.Date)Date
    ,CASE WHEN LXA.IsAutoBid=1 then cte.MaxBidPrice +LAD.BidIncrement else LXA.BidPrice End topay
FROM  LiveXAuctionBid LXA
INNER JOIN cte ON LXA.AuctionID = cte.AuctionID 
INNER JOIN LiveAuctionBid LA ON LA.LiveAuctionBidID = LXA.LiveAuctionBidID
INNER JOIN UserRegistration UR ON UR.UserRegistrationID = LA.UserID
INNER JOIN LiveAuctionDetails LAD ON LAD.LiveAuctionDetailID = LXA.AuctionID
INNER JOIN AuctionProduct AP ON AP.AuctionProductID = LAD.ProductID
WHERE LAD.LiveAuctionDetailID = @LiveAuctionDetailID
GROUP BY LXA.AuctionID,LAD.OpeningPrice,AP.Description,UR.Name,UR.ID,Date

Error in the Sql Query

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0