how to set NULLs to Zero

Question

Post reply

how to set NULLs to Zero

kat35601

SSC Eights!

Points: 825
More actions
July 13, 2016 at 1:51 pm

#311707

the nulls come from not having data for a certain year for a customer.
SELECT *
FROM (
SELECT DISTINCT cmoOrganizationID
,cmoName
,omlPartGroupID
,sum(omlExtendedPriceBase) AS total
,DATEPART(yyyy, ompOrderDate) AS Year_
FROM SalesOrders
LEFT OUTER JOIN SalesOrderLines ON omlSalesOrderID = salesorders.ompSalesOrderID
LEFT OUTER JOIN Organizations ON cmoOrganizationID = ompCustomerOrganizationID
LEFT OUTER JOIN OrganizationLocations ON cmoOrganizationID = cmlOrganizationID
AND cmlLocationID = ompShipLocationID
WHERE ompClosed = - 1
AND cmoOrganizationID IS NOT NULL
GROUP BY cmoOrganizationID
,cmoName
,ompOrderDate
,omlPartGroupID
) AS src
pivot(sum(total) FOR Year_ IN (
[2013]
,[2014]
,[2015]
,[2016]
)) piv
ORDER BY cmoOrganizationID
,cmoName
,omlPartGroupID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Try this:

SELECT cmoOrganizationID

,cmoName

,omlPartGroupID

,sum(CASE WHEN Year_ = 2013 THEN omlExtendedPriceBase ELSE 0 END) AS [2013]

,sum(CASE WHEN Year_ = 2014 THEN omlExtendedPriceBase ELSE 0 END) AS [2014]

,sum(CASE WHEN Year_ = 2015 THEN omlExtendedPriceBase ELSE 0 END) AS [2015]

,sum(CASE WHEN Year_ = 2016 THEN omlExtendedPriceBase ELSE 0 END) AS [2016]

FROM (

SELECT cmoOrganizationID

,cmoName

,omlPartGroupID

,sum(omlExtendedPriceBase) AS total

,DATEPART(yyyy, ompOrderDate) AS Year_

FROM SalesOrders

LEFT OUTER JOIN SalesOrderLines ON omlSalesOrderID = salesorders.ompSalesOrderID

LEFT OUTER JOIN Organizations ON cmoOrganizationID = ompCustomerOrganizationID

LEFT OUTER JOIN OrganizationLocations ON cmoOrganizationID = cmlOrganizationID

AND cmlLocationID = ompShipLocationID

WHERE ompClosed = - 1

AND cmoOrganizationID IS NOT NULL

GROUP BY cmoOrganizationID

,cmoName

,omlPartGroupID

) AS src

GROUP BY cmoOrganizationID

,cmoName

,omlPartGroupID

ORDER BY cmoOrganizationID

,cmoName

,omlPartGroupID;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ocean3300 SSChasing Mays Points: 621 More actions · Answer 2

If you want to display sum(omlExtendedPriceBase) as 0 if the value is null, use ISNULL(SUM(omlExtendedPriceBase),0).

SELECT *

FROM (

SELECT DISTINCT cmoOrganizationID

,cmoName

,omlPartGroupID

,ISNULL(sum(omlExtendedPriceBase),0) AS total

,DATEPART(yyyy, ompOrderDate) AS Year_

FROM SalesOrders

LEFT OUTER JOIN SalesOrderLines ON omlSalesOrderID = salesorders.ompSalesOrderID

LEFT OUTER JOIN Organizations ON cmoOrganizationID = ompCustomerOrganizationID

LEFT OUTER JOIN OrganizationLocations ON cmoOrganizationID = cmlOrganizationID

AND cmlLocationID = ompShipLocationID

WHERE ompClosed = - 1

AND cmoOrganizationID IS NOT NULL

GROUP BY cmoOrganizationID

,cmoName

,ompOrderDate

,omlPartGroupID

) AS src

pivot(sum(total) FOR Year_ IN (

[2013]

,[2014]

,[2015]

,[2016]

)) piv

ORDER BY cmoOrganizationID

,cmoName

,omlPartGroupID

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

ocean3300 (7/19/2016)
If you want to display sum(omlExtendedPriceBase) as 0 if the value is null, use ISNULL(SUM(omlExtendedPriceBase),0).

That's one of the many reasons why I don't use PIVOT. A good ol' fashioned CROSSTAB, like what Luis used, will take care of that auto-magically. Please see the following URLs for why I personally never use PIVOT.

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)