Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Question Unanswered: Basic SELECT command problem

    Hi guys / gals,

    I am having problems with this basic select statement, any help would be appreciated. I'm running DB2 v7 personal, on MS Win2K.

    As you can see, i'm trying to create a small db for book sales:

    SELECT category AS "Book Category", SUM(sales) AS "Quantity Sold", (price*sales) AS "Total Sales"
    FROM book b
    GROUP BY category
    ORDER BY category;

    sales - number of each book sold.
    category - genere of book.
    price - individual price of book.

    With this statement i want to produce a summary of the books sold for each category. But also show the total sales for each category (in currency). i'm trying to do this by multiplying the price of of each book by the number sold. However i'm currently getting the following error, and i don't know how to get around it, because I don't want to group the summary by price and sales, just the category.

    DBA2191E SQL execution error.

    com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/SUN] SQL0119N An expression starting with "PRICE" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803


    Thanks for any help or suggestions.

    Jim

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Basic SELECT command problem

    How about sum(price*sales)

    instead of

    price*sales

    HTH

    Sathyaram

    Originally posted by JIH2000
    Hi guys / gals,

    I am having problems with this basic select statement, any help would be appreciated. I'm running DB2 v7 personal, on MS Win2K.

    As you can see, i'm trying to create a small db for book sales:

    SELECT category AS "Book Category", SUM(sales) AS "Quantity Sold", (price*sales) AS "Total Sales"
    FROM book b
    GROUP BY category
    ORDER BY category;

    sales - number of each book sold.
    category - genere of book.
    price - individual price of book.

    With this statement i want to produce a summary of the books sold for each category. But also show the total sales for each category (in currency). i'm trying to do this by multiplying the price of of each book by the number sold. However i'm currently getting the following error, and i don't know how to get around it, because I don't want to group the summary by price and sales, just the category.

    DBA2191E SQL execution error.

    com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/SUN] SQL0119N An expression starting with "PRICE" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803


    Thanks for any help or suggestions.

    Jim
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Posts
    3

    Smile Re: Basic SELECT command problem

    Just a note to say thanks for the tip Sathyaram, it worked nicely! :-)

    Jim


    Originally posted by sathyaram_s
    How about sum(price*sales)

    instead of

    price*sales

    HTH

    Sathyaram

Posting Permissions

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