Ad:

Oracle Discussion Thread
Unsolved
Views: 1319

Oct 7th, 2009

Trouble with querying in Oracle using SQL

Expand Post »

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.

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:

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....

Similar Threads

let me know d steps to connect to oracle(sql) from vb6 in Visual Basic 4 / 5 / 6
Console Application : Oracle to SQL in VB.NET
Oracle Sql in Oracle
Oracle sql developer question in Oracle
i have installed oracle 9i sql but while installing forms & reports it gives errors t in Oracle

danielle23

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Feb 2009

Permalink

Oct 8th, 2009

Re: Trouble with querying in Oracle using SQL

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

The error message is self explanatory.

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Oct 8th, 2009

Re: Trouble with querying in Oracle using SQL

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.

danielle23

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Feb 2009

Permalink

Oct 9th, 2009

Re: Trouble with querying in Oracle using SQL

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

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Oct 13th, 2009

Re: Trouble with querying in Oracle using SQL

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

Now u try this query surely it will run

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).

Drlionheart

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

10 posts
since Oct 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: which language google made in

Next Thread in Oracle Forum Timeline: subtract 2 column value

DaniWeb Message

Cancel Changes

User Name
Password