Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

need to print zeros when no count exists

Posted on 2005-04-18
10
Medium Priority
?
226 Views
Last Modified: 2010-03-19
how can i print 0 when no counts exist, as opposed to just the blank lines:

select BuySell, count(*) AS '#',CONVERT(char(16),SUM(LastFillQuantity)) AS 'SUM'
FROM db.dbo.table
WHERE user=@userid
AND timeofexecution >= @starttime
group by buysell
0
Comment
Question by:RLLewis
  • 6
  • 4
10 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13808692
Try this:

select BuySell, count(*) AS '#',CONVERT(char(16),SUM(ISNULL(LastFillQuantity, 0))) AS 'SUM'
FROM db.dbo.table
WHERE user=@userid
AND timeofexecution >= @starttime
group by buysell
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13808759
no, it still doesn't return anything at all when the rowcount is zero
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13808798
Just to clarify what you mean.  You want to return all BuySell for the given user for all BuySell values even if the user has no record for it?  If this is the case, then you have to join it with a table that contains all BuySell values.

select A.BuySell, count(*) AS '#',CONVERT(char(16),SUM(ISNULL(B.LastFillQuantity, 0))) AS 'SUM'
FROM db.dbo.BuySell A left outer join db.dbo.table B
on A.BuySell = B.BuySell
WHERE user=@userid
AND timeofexecution >= @starttime
group by A.BuySell
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:RLLewis
ID: 13808885
no, i am doing the count/quantity for each user.  it is returned as this:

B     S
50   110

if there are no buys or sells, i just want it to return this:

B     S
0     0
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13808954
I don't think it is possible with just a single SELECT statement.  If it's ok with you not to use a single SELECT statement, this can do the trick:

IF EXISTS (SELECT 'X' FROM db.dbo.table WHERE user = @userid AND timeofexecution >= @starttime)
select BuySell, count(*) AS '#',CONVERT(char(16),SUM(LastFillQuantity)) AS 'SUM'
FROM db.dbo.table
WHERE user=@userid
AND timeofexecution >= @starttime
group by buysell
ELSE
SELECT 'B' AS BuySell, 0, '0'
UNION ALL
SELECT 'S' AS BuySell, 0, '0'
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13809015
well that gives me my zeros, but the '#' and 'SUM' is no longer output
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 500 total points
ID: 13809046
Try this one:

IF EXISTS (SELECT 'X' FROM db.dbo.table WHERE user = @userid AND timeofexecution >= @starttime)
select BuySell, count(*) AS '#',CONVERT(char(16),SUM(LastFillQuantity)) AS 'SUM'
FROM db.dbo.table
WHERE user=@userid
AND timeofexecution >= @starttime
group by buysell
ELSE
SELECT 'B' AS BuySell, 0 AS '#', '0' AS 'SUM'
UNION ALL
SELECT 'S' AS BuySell, 0 AS '#', '0' AS 'SUM'
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13809050
SELECT 'B' AS BuySell, 0 as '#' , '0' as 'SUM'
UNION ALL
SELECT 'S' AS BuySell, 0 AS '#', '0' as 'SUM'
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13809055
yep, got it.  thank you very much
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13834804
rafrancisco, if there are no buys or no sells, i'd like to do the same.  

B     1000    1892
S      0         0

but right now if there are no buys/sells, I’m getting just:
B     1000    1892

what's the best way to do this?
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question