Use of Count() Returning No Records

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 2008 Forums

Transact-SQL (2008)

Use of Count() Returning No Records

Author

Topic

cmomah
Starting Member

3 Posts

Posted - 2010-07-12 : 14:20:26

Hi Everyone, I’m trying to write a query such that when the Count(OrderId) returns no value, the value of “0” or “NULL” is returned

StoreId Region Volume
1234 North America 0
2345 Europe 0

Instead, I am getting this (i.e. the recordset is completely BLANK) as the result (and this breaks an Excel application that I created)

StoreId Region Volume

The query that I am using is as follows. What might I be missing?

select StoreId,
(CASE
WHEN StoreId = '1234' THEN 'North America'
WHEN StoreId = '2345' THEN 'Europe'
END) AS Region,
(CASE
WHEN (Count(OrderId)) < 1 THEN '0'
ELSE Count(OrderId)
END) AS Volume
from receipt
where StoreId in
(
'1234',
'2345'
)
GROUP by storeid

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-12 : 14:28:38

So you want to count something that isn't in the table?

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-07-12 : 14:37:32

You should have a [Region] table - select from Region LEFT OUTER JOIN Receipt. count(order) Group by Region. That will give you 0s for any region with no receipt rows.

Be One with the Optimizer
TG

cmomah
Starting Member

3 Posts

Posted - 2010-07-12 : 14:40:50

What I am counting is in the table

In the "Receipt" table, we have the following fields: StoreId, OrderID, along with other fields that are in the table.

I am counting the orderIds recorded under each storeId (I have mapped "StoreId" to "Region" as shown in above query)

When 0 orderIds are recorded for any storeid, I want it to write "0" or "NULL" to the resultset. instead it comes up blank

cmomah
Starting Member

3 Posts

Posted - 2010-07-12 : 14:44:00

quote:
Originally posted by TG

You should have a [Region] table - select from Region LEFT OUTER JOIN Receipt. count(order) Group by Region. That will give you 0s for any region with no receipt rows.

Be One with the Optimizer
TG

Thanks. Let me try this, and I will let you know of the outcome

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-13 : 11:28:38

Would a CTE work in this case ? Create a CTE to hold them to eliminate the need for another table...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources