why not showing nulls?

Question

Hi,

I have the following query:

 

SELECT     COUNT(dbo.UkPostcodes.Postcode) AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County,
                      dbo.UkPostcodes.Area, dbo.UkPostcodes.Country
FROM         dbo.UkPostcodes LEFT OUTER JOIN
                      dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''))
                      WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3)
                      ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END

GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country

The query should take all of the UK postcode regions (districts) and then match them up to the postcodes in a customer tbl...

What I'm trying to show is the count for postcode resutls, even the postcodes where there are no customers.

 

However it won't show any 0 counts - any ideas?

Answer 1

UPDATED:

hi,

The problem with the query seems to stem from a WHERE clause I have:

SELECT     ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2)
                      WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0)
                      AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
                      dbo.UkPostcodes.Master_Account
FROM         dbo.UkPostcodes LEFT OUTER JOIN
                      dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''))
                      WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3)
                      ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END
WHERE     (dbo.Customers_Trade.TradeCustomerID IN
                          (SELECT DISTINCT TradeCustomerID
                            FROM          dbo.Orders_Trade
                            WHERE      (DATEDIFF(d, order_date, GETDATE()) < 60))) AND (dbo.Customers_Trade.is_activated <> 'No') OR
                      (dbo.Customers_Trade.BillingAccountID <> '') AND (dbo.Customers_Trade.BillingAccountID IN
                          (SELECT DISTINCT TradeCustomerID
                            FROM          dbo.Orders_Trade
                            WHERE      (DATEDIFF(d, order_date, GETDATE()) < 60) AND (is_activated <> 'No')))
GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
                      dbo.Customers_Trade.Master_Account, dbo.UkPostcodes.Master_Account

 

 

does not show the nulls:

 

where as:

 

SELECT     ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2)
                      WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0)
                      AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
                      dbo.UkPostcodes.Master_Account
FROM         dbo.UkPostcodes LEFT OUTER JOIN
                      dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''))
                      WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3)
                      ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END
GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
                      dbo.Customers_Trade.Master_Account, dbo.UkPostcodes.Master_Account

 

does show them.

The where clause simply only brings in the customers who have ordered recently so are classed as "active"..

 

However, as the clause should only be affecting the Trade_Customers tbl, I can't see why that would affect the nulls

Answer 2

MTM81,

Did you still want help with this (7 years later)?

Thanks!

---

Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Answer 3

I believe the issue is this

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

and can be solved using below modification

SELECT     ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) 
                      WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0) 
                      AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country, 
                      dbo.UkPostcodes.Master_Account
FROM         dbo.UkPostcodes LEFT OUTER JOIN
                      dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) 
                      WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) 
                      ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END
WHERE     (dbo.Customers_Trade.TradeCustomerID IN
                          (SELECT DISTINCT TradeCustomerID
                            FROM          dbo.Orders_Trade
                            WHERE      (DATEDIFF(d, order_date, GETDATE()) < 60))) AND (dbo.Customers_Trade.is_activated <> 'No') OR
                      (dbo.Customers_Trade.BillingAccountID <> '') AND (dbo.Customers_Trade.BillingAccountID IN
                          (SELECT DISTINCT TradeCustomerID
                            FROM          dbo.Orders_Trade
                            AND (DATEDIFF(d, order_date, GETDATE()) < 60) AND (is_activated <> 'No')))
GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country, 
                      dbo.Customers_Trade.Master_Account, dbo.UkPostcodes.Master_Account

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs