homepage Welcome to WebmasterWorld Guest from 54.234.42.16
register, login, search, subscribe, help, library, PubCon, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library : Charter : Moderators: physics & whoisgregg

Databases Forum

    
Sort Data from table by month
Even when there is no data for that month
Red_Eye




msg:3819568		 4:44 pm on Jan 5, 2009 (gmt 0)

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

Any help would be appriciated

 

coopster




msg:3819615		 5:28 pm on Jan 5, 2009 (gmt 0)

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.
Red_Eye




msg:3819739		 8:34 pm on Jan 5, 2009 (gmt 0)

Thanks for getting back to me. I would quite like to do it in the database rather than the application. I had thought of adding a table call months with the following format

MonthID Name
1 jan
2 feb
3 mar

etc.

Then use an outer join to include all rows from the month table and a case select rule for any months with no data.

could you give me an example of how to code a union with the month numbers hard coded.

Thanks again for your help

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
WebmasterWorld ® and PubCon ® are a Registered Trademarks of Pubcon Inc.
© Pubcon Inc. 1996-2012 all rights reserved