question guy so poor question guy so poor - 7 months ago 13
SQL Question

SQL sorting date by year and month

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
JOIN Orderline
ON Orderline.ShopOrderID = ShopOrder.ShopOrderID
JOIN Book
ON Book.BookID = Orderline.BookID
JOIN Publisher
ON Publisher.PublisherID = Book.PublisherID
WHERE Publisher.name = 'Addison Wesley'
GROUP
BY ShopOrder.OrderDate
, Book.BookID
, Book.title
, Orderline.UnitSellingPrice
, Orderline.Quantity, book.Price
, Orderline.Quantity, ShopOrder.ShopOrderID
ORDER
BY ShopOrder.OrderDate


Please look at the picture

I want the query OrderDate group by year and month, so the data for the same month could be added together

Thanks a lot for your help

Answer

You need to extract the year and month from the date and use those in the select and group by columns. How you do this depends highly on the database. Many support functions called year() and month().

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, to_char(so.OrderDate, 'YYYY-MM').