Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2003
    Posts
    13

    Unanswered: Oracle As clause?

    Hi everyone,
    I am very new to Oracle and SQL, sp please take this under consideration when you read my question (and when you answer it
    I have a table Bid, and have columns such as Buyer (buyer ID) and Offer(bid offer) etc. Each buyer bidded in many items. I have to find the maximum of the total bids for all the buyers. In other words need to find the sums of the bids for each buyer and then find the max of those sums. The problem is that I cannot reference to the sums after i find it, in order to use it to find the max. I am doing smth like this:

    select sum(offer) as sum_offer from Bid where sum_offer >= (select max(offer) from Bid group by buyer)

    But the problem is that Oracle doesnt like AS clause or smth because I keep getting:
    ORA-00904: "SUM_OFFER": invalid identifier

    Is there any better way to do this? Or is there a way to fix this?

  2. #2
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Re: Oracle As clause?

    the following should work. Here I have used an inline view to the outer most FROM clause. It first created a view with the sum offer for each buyer and use that view to find the max value of that.

    SELECT MAX(sum_offer)
    FROM (SELECT SUM(offer) as sum_offer from Bid
    GROUP BY buyer)

    Cheers,
    Suren.

    Originally posted by albdolph
    Hi everyone,
    I am very new to Oracle and SQL, sp please take this under consideration when you read my question (and when you answer it
    I have a table Bid, and have columns such as Buyer (buyer ID) and Offer(bid offer) etc. Each buyer bidded in many items. I have to find the maximum of the total bids for all the buyers. In other words need to find the sums of the bids for each buyer and then find the max of those sums. The problem is that I cannot reference to the sums after i find it, in order to use it to find the max. I am doing smth like this:

    select sum(offer) as sum_offer from Bid where sum_offer >= (select max(offer) from Bid group by buyer)

    But the problem is that Oracle doesnt like AS clause or smth because I keep getting:
    ORA-00904: "SUM_OFFER": invalid identifier

    Is there any better way to do this? Or is there a way to fix this?

  3. #3
    Join Date
    Sep 2003
    Posts
    13

    Re: Oracle As clause?

    Thanks a lot!!!! That was such a relief, I have been looking into it for the past two days and couldnt find a solution!!!!

    Thanks again. a lot



    Originally posted by tm_suren
    the following should work. Here I have used an inline view to the outer most FROM clause. It first created a view with the sum offer for each buyer and use that view to find the max value of that.

    SELECT MAX(sum_offer)
    FROM (SELECT SUM(offer) as sum_offer from Bid
    GROUP BY buyer)

    Cheers,
    Suren.

  4. #4
    Join Date
    Sep 2003
    Posts
    13

    Re: Oracle As clause?

    Ok, now I have another problem. I need to show add only the highest bids for the same item that the same buyer must have bidded for. There is a case when the same buyer bids twice for the same item, and i need to only count the highest bid for that particular item when i do the total sum of bids. I am doing smth like get the max of the offer for each item, and then do the sum for each buyer and then find the max over that. The code looks smth like:


    select max(sum_offer)
    from (select max(offer) as max_offer
    from (select sum(max_offer) from Bid
    group by buyer) group by iid);

    But i'm having the same problems that you tried to fix for me. I get the invalid identifier for max_offer. Am i placing it somewhere where I shouldnt? Or is my logic the problem?


    Thank you very much for your time

  5. #5
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Re: Oracle As clause?

    I hope I got what you are trying to tell. Anyway try this and if not tell.

    SELECT SUM(max_offer),buyer
    FROM (SELECT MAX(offer) AS max_offer,buyer,iid
    FROM Bid
    GROUP BY buyer,iid)
    GROUP BY buyer

    Cheers,
    Suren.

  6. #6
    Join Date
    Sep 2003
    Posts
    13

    Re: Oracle As clause?

    Yes, you got it. thanks!!!
    the only thing is that like in the first case, i need to pick up the max of all of them now. right now it shows me the whole values for each buyer (along with the sum of their total bid). I need only the buyer's id who has the largest total, along with the value of that total.

    I really appreciate yur help. I hope im not wasting much of yur time.

    Originally posted by tm_suren
    I hope I got what you are trying to tell. Anyway try this and if not tell.

    SELECT SUM(max_offer),buyer
    FROM (SELECT MAX(offer) AS max_offer,buyer,iid
    FROM Bid
    GROUP BY buyer,iid)
    GROUP BY buyer

    Cheers,
    Suren.

  7. #7
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Re: Oracle As clause?

    I think following should do

    SELECT SUM(max_offer) AS tot_offer,buyer
    FROM (SELECT MAX(offer) AS max_offer,buyer,iid
    FROM Bid
    GROUP BY buyer,iid)
    GROUP BY buyer
    HAVINX MAX(tot_offer)

    if MAX(tot_offer) does not work try MAX(SUM(max_offer))

  8. #8
    Join Date
    Sep 2003
    Posts
    13

    Re: Oracle As clause?

    When i do exactly like written below it gives me this error:
    invalid relational operator right on the last ).

    When i do the max(sum(max_offer)) it gives me the famous error i was getting all day: group function is nested too deeply


    Sorry it isnt working

    Originally posted by tm_suren
    I think following should do

    SELECT SUM(max_offer) AS tot_offer,buyer
    FROM (SELECT MAX(offer) AS max_offer,buyer,iid
    FROM Bid
    GROUP BY buyer,iid)
    GROUP BY buyer
    HAVINX MAX(tot_offer)

    if MAX(tot_offer) does not work try MAX(SUM(max_offer))

  9. #9
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    How about this

    SELECT SUM(max_offer) AS tot_offer
    FROM (SELECT MAX(offer) AS max_offer,buyer,iid
    FROM Bid
    GROUP BY buyer,iid)
    WHERE ROWNUM < 2
    GROUP BY buyer
    ORDER BY tot_offer DESC

  10. #10
    Join Date
    Sep 2003
    Posts
    13
    Hmmm... There are two problems. the first is that it isnt ordering in DESC, because it gives as result the total bid of the first buyer, not the buyer's with highest value of the total bid.
    The second problem is that it isnt giving the buyer at all, it is only giving out one column... need the buyer with the highest total along with the value.

    lots of thank you for your help

    Originally posted by tm_suren
    How about this

    SELECT SUM(max_offer) AS tot_offer
    FROM (SELECT MAX(offer) AS max_offer,buyer,iid
    FROM Bid
    GROUP BY buyer,iid)
    WHERE ROWNUM < 2
    GROUP BY buyer
    ORDER BY tot_offer DESC

  11. #11
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: Oracle As clause?

    just a suggestion,

    SELECT MAX( SUM (offer) )
    FROM Bid
    GROUP BY buyer

    or what's wrong with such a query?
    ^/\x

  12. #12
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    SELECT MAX(tot_offer),buyer
    FROM (SELECT SUM(max_offer) AS tot_offer,buyer
    FROM (SELECT MAX(offer) AS max_offer,buyer,iid
    FROM Bid
    GROUP BY buyer,iid)
    GROUP BY buyer)

    Be aware of the performance issues is this works !

  13. #13
    Join Date
    Sep 2003
    Posts
    13

    Re: Oracle As clause?

    It gives an error like:
    group function is nested too deeply... oracle doesnt like such thing i guess!!!!


    Originally posted by berxh3g
    just a suggestion,

    SELECT MAX( SUM (offer) )
    FROM Bid
    GROUP BY buyer

    or what's wrong with such a query?

  14. #14
    Join Date
    Sep 2003
    Posts
    13
    It gives an error on the first line. It says:
    not a single-group group function

    I guess it doesnt like the buyer there!!!

    Originally posted by tm_suren
    SELECT MAX(tot_offer),buyer
    FROM (SELECT SUM(max_offer) AS tot_offer,buyer
    FROM (SELECT MAX(offer) AS max_offer,buyer,iid
    FROM Bid
    GROUP BY buyer,iid)
    GROUP BY buyer)

    Be aware of the performance issues is this works !

  15. #15
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    My mistake. Sorry. Try this.

    SELECT MAX(tot_offer),buyer
    FROM (SELECT SUM(max_offer) AS tot_offer,buyer
    FROM (SELECT MAX(offer) AS max_offer,buyer,iid
    FROM Bid
    GROUP BY buyer,iid)
    GROUP BY buyer)
    GROUP BY buyer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •