How to add 0 in decode in a subquery with a Count(*) function?

Question

This is the subquery but I am trying to find a way to add Zeros in this subquery... as there are 100s of totals and columns and this is one of the several subqueries in it. I tried to add a decode but it did not work I probably did it wrongly.

(select rd.rpt_nm as rep_id, count(*) as SW

from rpt_descriptor rd,

rpt_usage ru,

employee_display ed

where

ed.EMPLOYEE_ID = ru.LAST_USER_ID

and rd.RPT_NM = ru.rpt_nm

and

ed.home_region_nm = 'Southwest'

group by rd.rpt_nm) sub12,

Answer 1

I'm not clear on what you're asking.  Since you're using count(*) it is going to count the number of records without paying attention to the values (i.e. 0s and nulls will be included with count(*)).  If you're saying you want to have leading zeros on the result (i.e. 00013 instead of 13), here's a trick that I use.

Cast the number to a string, then add it to a string of zeros and then do a right() on it.  The number of zeros in the string should be (desired length - 1), so if you want the final number to be 5 long, prepend the converted number with four zeros, then take the right 5 characters.  Example:

select count(*) from blah    returns:

13

select right('0000' + cast(count(*) as varchar(10)), 5)    returns:

00013

See?  I added the converted number to four (5-1) zeros ("0000") and then did a right() with 5.  You should realize that this new result is a string and is no longer a number.  I hope that is what you were asking.