ORACLE Query Help

jo15765 · #1 (**permalink**) 02-12-12, 21:59

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

LKBrwn_DBA · #2 (**permalink**) 02-13-12, 16:21

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;

jo15765 · #3 (**permalink**) 02-13-12, 16:45

Oh such a simple mistake! Thank you for your assistance

Internet Services

Web Development

Digital Marketing

Consumer Tech