Trouble with querying in Oracle using SQL

[danielle23]

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.

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:

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

[debasisdas]

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

The error message is self explanatory.

[danielle23]

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.

[debasisdas]

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

[Drlionheart]

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

Now u try this query surely it will run

SELECT ordernum, SUM(itemprice*quantity)
AS totalcost FROM orderline,stockitem
WHERE orderline.itemid = stockitem.itemid
 AND ordernum = 'P105'
GROUP BY ordernum;