0

While given the publisher name, I wanted to produce a report of books ordered by year and month. For each year and month the report should show bookid, title, total number of orders for the title, total quantity and total selling value (both order value and retail value) while given the book ID

My code :

SELECT 
    ShopOrder.OrderDate, Book.BookID, Book.title, 
    COUNT(ShopOrder.ShopOrderID) AS "Total number of order", 
    SUM (Orderline.Quantity) AS "Total quantity", 
    Orderline.UnitSellingPrice * Orderline.Quantity AS "Total order value", 
    Book.Price * OrderLine.Quantity AS "Total retail value" 
FROM 
    ShopOrder, Publisher, Book, Orderline 
WHERE 
    Publisher.name = 'Penguin Books' 
    AND ShopOrder.ShopOrderID = Orderline.ShopOrderID 
    AND Book.BookID = Orderline.BookID 
    AND Publisher.PublisherID = Book.PublisherID 
ORDER BY 
    ShopOrder.OrderDate;

Error:

column "shoporder.orderdate" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT ShopOrder.OrderDate, Book.BookID, Book.title, COUNT(S... ^

ERROR: column "shoporder.orderdate" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 8

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
question guy so poor
  • 7
  • 2
  • Are you sure there is a table or view called orderline? – n8wrl Apr 29 '16 at 00:33
  • http://stackoverflow.com/questions/695289/cannot-simply-use-postgresql-table-name-relation-does-not-exist - You're not referencing the table name correctly. Also you can alias your table names so you don't have to write them over and over again (I know that's not part of the problem but it'll shorten your query and make it easier to read). – Dresden Apr 29 '16 at 00:35
  • Edited the Error, sorry about the previous mistake – question guy so poor Apr 29 '16 at 00:56
  • Thanks for the status report, showing the query and the error. Did you have a *question*? – spencer7593 Apr 29 '16 at 01:05
  • 1
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged – marc_s Apr 29 '16 at 04:46
  • And also: please tag which **actual**, concrete RDBMS you're using. SQL is just the query language, and many things and features are vendor-specific, so it makes a difference whether you're using `oracle`, `postgresql`, `mysql`, `sql-server` or whatever else you might be using – marc_s Apr 29 '16 at 04:47
  • The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. – jarlh Apr 29 '16 at 07:37

2 Answers2

0

Check whether you have table named as orderline. To check it execute following query

select * from orderline;

Most likely, you don't have such table.

Jigar
  • 468
  • 1
  • 5
  • 15
0

Thanks for the status report. Did you have a question?

Once you get the identifier issue resolved, your query is going to encounter an error about non-aggregates in your SELECT list which aren't in a GROUP BY clause.

It's 2016. It's way past time to ditch the old-school comma operator for the join operation and use the JOIN keyword with the join predicates moved to an appropriate ON clause.

The most likely cause for this error (and this is only a guess) is that when the "Orderline" table was created, the name of the table was enclosed in double quotes, and there was at least one upper case letter in the table name. Now that table name is case sensitive, references to the table must be enclosed in double quotes, and the name of the table must match exactly, including case.

EDIT due to updated question

Before you add aggregate functions to your query, I recommend you verify that the query is returning the rows you expect.

SELECT s.OrderDate
     , b.BookID
     , b.title
     , s.ShopOrderID
     , o.Quantity
     , o.UnitSellingPrice * o.Quantity  AS "Total order value"
     , b.Price * o.Quantity             AS "Total retail value" 
  FROM ShopOrder s
  JOIN Orderline o
    ON o.ShopOrderID = s.ShopOrderID
  JOIN Book
    ON b.BookID = o.BookID 
  JOIN Publisher p
    ON p.PublisherID = b.PublisherID 
 WHERE p.name = 'Penguin Books' 
 ORDER
    BY s.OrderDate
     , s.ShopOrderID
     , b.BookID
     , o.Quantity

Then, you can work on adding aggregate function. Any expressions in the SELECT list that aren't aggregates (MAX(), MIN(), SUM(), COUNT()) must appear in a GROUP BY clause.

SELECT b.BookID
     , b.title
     , SUM(o.UnitSellingPrice * o.Quantity)  AS "Total order value"
     , SUM(b.Price * o.Quantity)             AS "Total retail value" 
  FROM ShopOrder s
  JOIN Orderline o
    ON o.ShopOrderID = s.ShopOrderID
  JOIN Book
    ON b.BookID = o.BookID 
  JOIN Publisher p
    ON p.PublisherID = b.PublisherID 
 WHERE p.name = 'Penguin Books' 
 GROUP
    BY b.BookID
     , b.title
 ORDER
    BY b.BookID
     , b.title
spencer7593
  • 106,611
  • 15
  • 112
  • 140