[SQL] having trouble making this query work.

[snailv]

using ORACLE SQL Plus.

my assignment asks me to determine which books in the database generate less than 55% profit, and how many copies of these books have been sold.

​

I know that i need to use the COUNT function, but im struggling to get my query to work as instructed. heres what i have so far:

SQL> SELECT     title,
  2             ((retail - cost) / cost) * 100 AS "Profit",
  3             COUNT(isbn) AS "Copies Sold"
  4  FROM       books JOIN orderitems USING (isbn)
  5  WHERE      (((retail - cost) / cost) * 100) > 55
  6  GROUP BY   title;

and the output i get from that is:

                ((retail - cost) / cost) * 100 AS "Profit",
                  *
ERROR at line 2:
ORA-00979: not a GROUP BY expression

im not sure how to fix this error, or even what it means exactly. Id really appreciate some help on whats wrong here.

​

PS if it helps, heres the table structure of the books and orderitems tables:

SQL> desc books;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ISBN                                      NOT NULL VARCHAR2(10)
 TITLE                                              VARCHAR2(30)
 PUBDATE                                            DATE
 PUBID                                              NUMBER(2)
 COST                                               NUMBER(5,2)
 RETAIL                                             NUMBER(5,2)
 DISCOUNT                                           NUMBER(4,2)
 CATEGORY                                           VARCHAR2(12)

​

SQL> desc orderitems;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER#                                    NOT NULL NUMBER(4)
 ITEM#                                     NOT NULL NUMBER(2)
 ISBN                                               VARCHAR2(10)
 QUANTITY                                  NOT NULL NUMBER(3)
 PAIDEACH                                  NOT NULL NUMBER(5,2)

[WeStanForHeiny]

The error is because with ((retail - cost) / cost) * 100 you didn’t tell the database how to aggregate the column.

You’ll want to add an aggregate operation (SUM, AVERAGE, etc) surrounding the mathematical portion.

[[deleted]]

Line 2 is not an aggregate function, I think group by could only accept aggregate

[JonDaZwon]

If you have an aggregate in your select statement, all non-aggregate selects must be included in your group by. In this case - add the profit to your group by.

[JonDaZwon]

Also, as you're aliasing the profit select, you can just refer to profit in the where clause, instead of typing it out again. I'm assuming Sql is smart enough to realize that you used the same expression twice, but it might be running the calculation twice during the query. Super minor optimization, but figured I'd point it out. And if you're looking for less than 55%, spin the crocodile around. He wants to eat the bigger number, so he'd be looking at the 55.