I have an orders table, I want to get a monthly totals for this table. But some months might not have any orders in so I want it to return 0 for these months.
Jan 0 feb 10 Mar 20 Apr 0 May 50 Jun 10 Jul 10 Aug 0 Sep 0 Oct 9 Nov 33 Dec 22
The Query I have at the moment is
SELECT MONTH(Datecomplete) AS MONTHNO, DATENAME(month,DateComplete) AS MonthComplete, SUM(Price*QTY) AS Total FROM OrderParts INNER JOIN Orders ON OrderParts.OrderID = Orders.OrderID WHERE AND YEAR(DateComplete)=@Year GROUP BY MONTH(Datecomplete),DATENAME(month,DateComplete) ORDER BY MONTHNO
There are quite a few options ... one may be that you can modify your query by using something like a UNION with each month number coded. Another may be that you can use your application logic to show a zero sum total for any months with no corresponding totals.