CODEWITHSUNDEEP
sql-server
AnandMeena
AnandMeena

Reputation: 578

Sql query to get default value as 0 for a particular column

I am facing a issue with a query.

My query is :-

SELECT MONTH(o.OrderDate) as MonthValue, 
       YEAR(o.OrderDate) as YearValue, 
       C.CustomerTypeID, Count(o.Total) as NoOfOrders
FROM Orders o        
RIGHT JOIN Customers C on C.CustomerID = o.CustomerID      

WHERE o.OrderDate >= CONVERT(DATETIME, '1/1/2013 00:00:00 AM')
AND o.OrderDate <= CONVERT(DATETIME, '12/31/2013 23:59:59 PM')
GROUP BY MONTH(o.OrderDate), 
         YEAR(o.OrderDate), 
         C.CustomerTypeID 
ORDER BY MONTH(o.OrderDate),
         YEAR(o.OrderDate),
         C.CustomerTypeID

It is giving result as follows :-

MonthValue   YearValue   CustomerTypeID   NoOfOrders
1              2013            1             10
1              2013            2             20
1              2013            3             45
2              2013            1             45
2              2013            2             45
3              2013            1             88
3              2013            2             56
3              2013            3             89

As for month 2, customer type 3 has no result, so it is not appears in result.

But I want to show "0" as a default result for it, like below :-

2              2013            3             0

Thanks in advance.

Upvotes: 0

Views: 207

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

EDITED: You must outer join all months do get the missing entries. So for best readability cross join all customers and months first and then outer join the orders.

SELECT all_months.MonthValue, 
       all_months.YearValue, 
       C.CustomerTypeID,
       Count(o.Total) as NoOfOrders
FROM 
(
  SELECT distinct MONTH(OrderDate) as MonthValue, YEAR(OrderDate) as YearValue
  FROM orders 
  WHERE YEAR(OrderDate) = 2013
) all_months        
CROSS JOIN Customers C 
LEFT OUTER JOIN Orders o 
  ON o.CustomerID = C.CustomerID 
  AND MONTH(o.OrderDate) = all_months.MonthValue 
  AND YEAR(o.OrderDate) = all_months.YearValue
GROUP BY all_months.MonthValue, 
         all_months.YearValue, 
         C.CustomerTypeID 
ORDER BY all_months.MonthValue,
         all_months.YearValue,
         C.CustomerTypeID ;

Upvotes: 1

Nisarg Chauhan
Nisarg Chauhan

Reputation: 305

Try this :

SELECT  2013 as [Year],
        months.number,
        Amount = SUM(COALESCE(o.Total,0)),
        C.CustomerType
FROM    Customers C
CROSS JOIN
(SELECT number FROM master..spt_values WHERE type='p' and number between 1 and 12) months
LEFT JOIN [Orders] o on C.CustomerId = o.CustomerId and YEAR(o.OrderDate) = 2013 and MONTH(o.OrderDate) = months.number
GROUP BY months.number, C.CustomerType 
ORDER BY months.number, C.CustomerType

Upvotes: 4

Peter Davidsen
Peter Davidsen

Reputation: 678

Well you could probably do a full outer join which should give you all of the customers, retuning null for the missing data. You could then select ISNULL(NoOfOrders, 0) to get 0 instead of a null.

I'm not 100& sure that would work, but you could give it a try.

Upvotes: 0

Related Questions