Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    16

    Unanswered: Help with this query please..

    I have a list of books, categories and their retail cost. I wrote a query that list that Total retail of each category and the Average of each category.

    Code:
    SELECT category AS "Category", TO_CHAR(SUM(retail), '$999,999.99') "Total Retail",TO_CHAR(AVG(retail), '$999,999.99') "Average Retail"
    	FROM books
    	WHERE 'Total Retail' > '40'
    	GROUP BY category;
    This is the result, which has the correct amounts.

    Category Total Retail Average Reta
    ------------ ------------ ------------
    COMPUTER $211.40 $52.85
    COOKING $48.70 $24.35
    CHILDREN $68.90 $34.45
    LITERATURE $39.95 $39.95
    BUSINESS $31.95 $31.95
    FITNESS $30.95 $30.95
    FAMILY LIFE $111.95 $55.98
    SELF HELP $29.95 $29.95


    In the WHERE I am trying to filter out the categories with total retail of less than 40. As you can see it is not taking the WHERE into consideration and I am not sure why. Do you have any advice?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try by replacing WHERE clause with HAVING clause and use the expression rather than column-name in select clause, like...
    HAVING SUM(retail) > 40

    Your original query compared character strings 'Total Retail' and '40' and it is always true regardless the values in table.
    Last edited by tonkuma; 02-17-12 at 02:57.

  3. #3
    Join Date
    Jan 2012
    Posts
    16
    Quote Originally Posted by tonkuma View Post
    Please try by replacing 'Total Retail' in WHERE clause with the expression in select-list, like...
    WHERE TO_CHAR(retail), '$999,999.99') > 40

    Your original query compared character strings 'Total Retail' and '40' and it is always true regardless the values in table.
    I tried that already and I got an Error of

    SQL> SELECT category AS "Category", TO_CHAR(SUM(retail), '$999,999.99') "Total Retail",TO_CHAR(AVG(r
    etail), '$999,999.99') "Average Retail"
    2 FROM books
    3 WHERE TO_CHAR(retail), '$999,999.99') > 40
    4 GROUP BY category;
    WHERE TO_CHAR(retail), '$999,999.99') > 40
    *
    ERROR at line 3:
    ORA-00920: invalid relational operator

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Jsin View Post
    As you can see it is not taking the WHERE into consideration and I am not sure why.
    It surely is - but as you use two string literals, and in lexicographical order, 'Total Retail' is greater than '40' (as 'T' > '4' - think of your vocabulary), it is always true.
    Maybe you are confusing it with "Total Retail" (column alias name), but you cannot use it in the same query except ORDER BY clause.
    Quote Originally Posted by Jsin View Post
    Do you have any advice?
    Consider using HAVING clause instead of WHERE.
    Use the function for computing SUM there directly (without TO_CHAR - it just converts it to text), not text nor column alias.
    Compare it with number, not string ('40' is string literal, numeric one is 40 - without single quotes).

    Everything is described in SQL Language Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
    Please, consult it firstly.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Jsin View Post
    I tried that already and I got an Error of

    SQL> SELECT category AS "Category", TO_CHAR(SUM(retail), '$999,999.99') "Total Retail",TO_CHAR(AVG(r
    etail), '$999,999.99') "Average Retail"
    2 FROM books
    3 WHERE TO_CHAR(retail), '$999,999.99') > 40
    4 GROUP BY category;
    WHERE TO_CHAR(retail), '$999,999.99') > 40
    *
    ERROR at line 3:
    ORA-00920: invalid relational operator
    I'm sorry. I was jumped to a wrong conclusion.
    I have edited and revised my previous post.

  6. #6
    Join Date
    Jan 2012
    Posts
    16
    Thanks guys I got it to work...

    Code:
    SELECT category AS "Category", TO_CHAR(SUM(retail), '$999,999.99') "Total Retail",TO_CHAR(AVG(retail), '$999,999.99') "Average Retail"
    	FROM books
    	HAVING SUM(retail) > 40
    	GROUP BY category;
    So I use HAVING when I need to sort by Group functions, is this correct?

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    NO the having clause allows you to filter the results of the return values such as sum(retail). Group by does not do sorting except by chance. Your select should have the clause ORDER BY CATEGORY as the last line of the select. If you are trying to exclude any retail amount $40 or under from concideration you would do

    Code:
    SELECT category AS "Category", 
                TO_CHAR(SUM(retail), '$999,999.99') "Total Retail",
               TO_CHAR(AVG(retail), '$999,999.99') "Average Retail"
        FROM books
        where retail > 40
        GROUP BY category
        order by category;
    Last edited by beilstwh; 02-17-12 at 10:26.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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