Help with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a query

Author

Topic

Bananadon
Starting Member

3 Posts

Posted - 2009-01-19 : 11:49:16

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

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-19 : 12:26:03

Well if those values are null and you want to replace them with a zero the easiest way I think would be to use the ISNULL function.

Here is a link (its pretty simple to use).
[url]http://msdn.microsoft.com/en-us/library/ms184325.aspx[/url]

Here is the syntax that would return 0 if Price * Qty returns null.
select ISNULL(SUM(Price*QTY),0)

Hope that helps !

r&r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 12:32:17

[code]
SELECT mnth.Month,
COALESCE(t.Total,0) AS Total
FROM
(SELECT 'Jan' AS Month,1 AS Ord UNION ALL
SELECT 'Feb',2 UNION ALL
SELECT 'Mar',3 UNION ALL
..
SELECT 'Dec',12
)mnth
LEFT JOIN (
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)
)t
ON t.MonthComplete=mnth.Month
ORDER BY mnth.Ord
[/code]

Bananadon
Starting Member

3 Posts

Posted - 2009-01-20 : 05:57:57

visakh16

Thank you that solved the problem.
Now I know how to do it, I am sure it will be useful in the future

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources