Oracle

Trouble with querying in Oracle using SQL

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Feb 2009

Posts: 18

Reputation: danielle23 is an unknown quantity at this point

Solved Threads: 0

danielle23 danielle23 is offline

Offline

Newbie Poster

Trouble with querying in Oracle using SQL

Oct 7th, 2009

I am working on an Oracle homework assignment, working in shell, trying to query a tricky display question my professor has asked for. I will show both tables, and what I have to execute.

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
SQL> describe orderline
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 LINENUM                                   NOT NULL NUMBER(3)
 ORDERNUM                                  NOT NULL CHAR(4)
 ITEMID                                             CHAR(3)
 QUANTITY                                           NUMBER(4)
 
SQL> SELECT * FROM orderline;
 
   LINENUM ORDE ITE   QUANTITY
---------- ---- --- ----------
         1 P111 B57         10
         3 P100 B53          1
         2 P100 B57          3
         2 P105 C91          1
         2 P111 X41          3
         1 P109 C91          4
         1 P100 X41          5
         1 P105 B57         10
 
8 ROWS selected.
 
SQL> describe stockitem
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 ITEMID                                    NOT NULL CHAR(3)
 ITEMNAME                                           CHAR(8)
 ITEMPRICE                                          NUMBER(5,2)
 
SQL> r
  1  SELECT * FROM orderline;
 
   LINENUM ORDE ITE   QUANTITY
---------- ---- --- ----------
         1 P111 B57         10
         3 P100 B53          1
         2 P100 B57          3
         2 P105 C91          1
         2 P111 X41          3
         1 P109 C91          4
         1 P100 X41          5
         1 P105 B57         10
 
8 ROWS selected.
SQL> describe orderline
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LINENUM                                   NOT NULL NUMBER(3)
 ORDERNUM                                  NOT NULL CHAR(4)
 ITEMID                                             CHAR(3)
 QUANTITY                                           NUMBER(4)

SQL> select * from orderline;

   LINENUM ORDE ITE   QUANTITY
---------- ---- --- ----------
         1 P111 B57         10
         3 P100 B53          1
         2 P100 B57          3
         2 P105 C91          1
         2 P111 X41          3
         1 P109 C91          4
         1 P100 X41          5
         1 P105 B57         10

8 rows selected.

SQL> describe stockitem
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ITEMID                                    NOT NULL CHAR(3)
 ITEMNAME                                           CHAR(8)
 ITEMPRICE                                          NUMBER(5,2)

SQL> r
  1  select * from orderline;

   LINENUM ORDE ITE   QUANTITY
---------- ---- --- ----------
         1 P111 B57         10
         3 P100 B53          1
         2 P100 B57          3
         2 P105 C91          1
         2 P111 X41          3
         1 P109 C91          4
         1 P100 X41          5
         1 P105 B57         10

8 rows selected.

The question on the homework reads: Write a query that will display the total cost of order P105 (answer is 79.65)

I wrote this code:

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
SQL> r
  1  SELECT ordernum, SUM(itemprice*quantity)
  2  AS totalcost
  3  FROM orderline,stockitem
  4  WHERE orderline.itemid = stockitem.itemid
  5* AND ordernum = 'P105';
SQL> r
  1  select ordernum, sum(itemprice*quantity)
  2  as totalcost
  3  from orderline,stockitem
  4  where orderline.itemid = stockitem.itemid
  5* and ordernum = 'P105';

I am receiving this error:
ERROR at line 1:
ORA-00937: not a single-group group function

I am stumped. I have tried numerous versions of this code, but this one in particular makes the most sense to me. Please help me if what is wrong here seems to jump out at you!!

Thank you....

•

Join Date: Feb 2007

Posts: 2,122

Reputation: debasisdas will become famous soon enough

Solved Threads: 129

debasisdas debasisdas is offline

Offline

Postaholic

Oct 8th, 2009

Sorry, we do not solve home works /assignments here.

The error message is self explanatory.

Share your Knowledge.

•

Join Date: Feb 2009

Posts: 18

Reputation: danielle23 is an unknown quantity at this point

Solved Threads: 0

danielle23 danielle23 is offline

Offline

Newbie Poster

Oct 8th, 2009

Actually, I believe that it is within the rules that if you show that you've done the work, and still having trouble with the syntax, people are certainly able to assist the person in need. This is not the first time I have come here for help and received just the push I needed to solve my problem. After all, isn't that what these forums are for?

I certainly was not asking anyone to do the work for me, as I clearly have my own attempted code placed here.

All that aside though, I did in fact figure it out in the end. Thanks anyway.

•

Join Date: Feb 2007

Posts: 2,122

Reputation: debasisdas will become famous soon enough

Solved Threads: 129

debasisdas debasisdas is offline

Offline

Postaholic

Oct 9th, 2009

Just try adding a GROUP BY statement to your existing query.

Share your Knowledge.

•

Join Date: Oct 2009

Posts: 10

Reputation: Drlionheart is an unknown quantity at this point

Solved Threads: 1

Drlionheart Drlionheart is offline

Offline

Newbie Poster

Oct 13th, 2009

Hello
for every summary column in sql , you have to use 'GROUP BY' condition

Now u try this query surely it will run

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT ordernum, SUM(itemprice*quantity)
AS totalcost FROM orderline,stockitem
WHERE orderline.itemid = stockitem.itemid
 AND ordernum = 'P105'
GROUP BY ordernum;
SELECT ordernum, SUM(itemprice*quantity)
AS totalcost FROM orderline,stockitem
WHERE orderline.itemid = stockitem.itemid
 AND ordernum = 'P105'
GROUP BY ordernum;

Last edited by peter_budo; Oct 13th, 2009 at 1:46 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks).