Mike Terpeza Mike Terpeza - 9 months ago 24
SQL Question

Creating an SQL query that eliminates duplicate months/year

Hello Stack Overflow community - hopefully i'm on the right track with this one, but i'm trying to write a query where a report out shows the number of orders placed by month/year. The report currently brings up all the days where i'm trying to join them all by month/year collectively. Hopefully this makes sense, i'm pretty new to this, be gentle please ;)

select distinct month(o.orderdate) 'Month',
year(o.orderdate) 'Year', sum(od.Quantity) as Orders
from OrderDetails od
join Products p
on od.ProductID = p.ProductID
join Orders o
on od.OrderID = o.OrderID
group by o.orderdate
Order by year, month asc;

Answer Source

You need to group by what you want to define each row. In your case, that is the year and month:

select year(orderdate) as yyyy, month(o.orderdate) as mm,
       sum(od.Quantity) as Orders 
from OrderDetails od join
     Products p
     on od.ProductID = p.ProductID join
     Orders o
     on od.OrderID = o.OrderID
group by year(o.orderdate), month(o.orderdate) 
Order by yyyy, mm asc;

Notes:

  • I changed the column names to yyyy and mm so they do not conflict with the reserved words year and month.
  • Don't use single quotes for column aliases. This is a bad habit that will eventually cause problems in your query.
  • I always use as for column aliases (to help prevent missing comma mistakes), but never for table aliases.
  • The product table is not needed for this query.

Edit: If you want a count of orders, which your query suggests, then this might be more appropriate:

select year(o.orderdate) as yyyy, month(o.o.orderdate) as mm,
       count(*) as Orders 
from orders o
group by year(o.orderdate), month(o.orderdate) 
Order by yyyy, mm asc;