need to print zeros when no count exists

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
LVL 1
RLLewisAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
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
 
rafranciscoCommented:
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
 
RLLewisAuthor Commented:
no, it still doesn't return anything at all when the rowcount is zero
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
rafranciscoCommented:
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
 
RLLewisAuthor Commented:
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
 
rafranciscoCommented:
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
 
RLLewisAuthor Commented:
well that gives me my zeros, but the '#' and 'SUM' is no longer output
0
 
RLLewisAuthor Commented:
SELECT 'B' AS BuySell, 0 as '#' , '0' as 'SUM'
UNION ALL
SELECT 'S' AS BuySell, 0 AS '#', '0' as 'SUM'
0
 
RLLewisAuthor Commented:
yep, got it.  thank you very much
0
 
RLLewisAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.