Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: ORACLE Query Help

    I am using this query:
    Code:
    select category, '$'||sum(cost)AS "Total Retail" ,'$'||avg(cost) as "Average Retail" from books  group by category having sum(cost)>40;
    I also tried this query and I got an ERROR of group function not allowed here:
    Code:
    select category, '$'|| sum(cost) as "Total Retail",'$'|| avg(cost) as "Average Retail" from books WHERE sum(cost)>40;
    And this is returning 3 results....the correct query should return 4 results. Can someone point out where my error is?


    ****Disregard I was using the wrong field name for the sum/avg....Simple mistakes make life hell from time to time
    Last edited by jo15765; 02-12-12 at 23:23.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Better use the TO_CHAR() function for formatting the amounts:
    Code:
    SELECT category
         , TO_CHAR(SUM( cost ),'$999,990.00') AS "Total Retail"
         , TO_CHAR(AVG( cost ),'$999,990.00') AS "Average Retail"
      FROM books
     GROUP BY category
    HAVING SUM( cost ) > 40;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Oh such a simple mistake! Thank you for your assistance

Posting Permissions

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