, 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"
ON Orderline.ShopOrderID = ShopOrder.ShopOrderID
ON Book.BookID = Orderline.BookID
ON Publisher.PublisherID = Book.PublisherID
WHERE Publisher.name = 'Addison Wesley'
, Orderline.Quantity, book.Price
, Orderline.Quantity, ShopOrder.ShopOrderID
You need to extract the year and month from the date and use those in the
group by columns. How you do this depends highly on the database. Many support functions called
Then you need to just aggregate by the fields that you want. Something like this:
SELECT YEAR(so.OrderDate) as yyyy, MONTH(so.OrderDate) as mm, b.BookID, b.title, COUNT(so.ShopOrderID) AS "Total number of order", SUM(ol.Quantity) AS "Total quantity", SUM(ol.UnitSellingPrice * ol.Quantity AS "Total order value", SUM(b.Price * ol.Quantity) AS "Total retail value" FROM ShopOrder so JOIN Orderline ol ON ol.ShopOrderID = so.ShopOrderID JOIN Book b ON b.BookID = ol.BookID JOIN Publisher p ON p.PublisherID = b.PublisherID WHERE p.name = 'Addison Wesley' GROUP BY YEAR(so.OrderDate), MONTH(so.OrderDate), b.BookID, b.title ORDER BY MIN(so.OrderDate)
Note the use of table aliases makes the query easier to write and to read.
The above works in MySQL, DB2, and SQL Server. In Postgres and Oracle,