Remove 0 from SQL result

Hello All
I am attaching the stored procedure and the output please help me to remove the 0s from the result.
senz



Mod edit: see http:#a34118133 for replacement attachment
ALTER PROCEDURE [dbo].[spTop20Server] 
	-- Add the parameters for the stored procedure here
	@TechType varchar(50),
	@Domain varchar(50),
	@Month varchar(20)
AS
BEGIN
	SET NOCOUNT ON;

   select top 20 * from  
		( 
		  SELECT elementname, 
				 COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, 
				 COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, 
				 COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, 
				 COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, 
				 COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, 
				 COUNT(*) AS grandtotal 
			FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and 
								sourcedomainname like '%' + @Domain + '%' and 
								DATENAME(MM,snhinsertedat) =  @Month 
		GROUP BY elementname 
		) as t order by grandtotal desc
END

Open in new window

LVL 4
Senz79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
don't you want to look at 0 in sev1,2 etc. fields?



ALTER PROCEDURE [dbo].[spTop20Server] 
        -- Add the parameters for the stored procedure here
        @TechType varchar(50),
        @Domain varchar(50),
        @Month varchar(20)
AS
BEGIN
        SET NOCOUNT ON;

   select top 20 
		elementname,
		case when sev1=0 then NULL else sev1 end as sev1, 
		case when sev2=0 then NULL else sev2 end as sev2,
		case when sev3=0 then NULL else sev3 end as sev3,
		case when sev4=0 then NULL else sev4 end as sev4,
		case when sev5=0 then NULL else sev5 end as sev5
	from  
   ( 
      SELECT elementname, 
                     COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, 
                     COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, 
                     COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, 
                     COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, 
                     COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, 
                     COUNT(*) AS grandtotal 
            FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and 
                                                    sourcedomainname like '%' + @Domain + '%' and 
                                                    DATENAME(MM,snhinsertedat) =  @Month 
    GROUP BY elementname 
    ) as t order by grandtotal desc
END

Open in new window

0
Senz79Author Commented:
no there is no zero in the column
0
RiteshShahCommented:
if you don't want to see the row if any of them have 0 in sev fields, than use this one

ALTER PROCEDURE [dbo].[spTop20Server] 
        -- Add the parameters for the stored procedure here
        @TechType varchar(50),
        @Domain varchar(50),
        @Month varchar(20)
AS
BEGIN
        SET NOCOUNT ON;

   select top 20 * from  
                ( 
                  SELECT elementname, 
                                 COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, 
                                 COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, 
                                 COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, 
                                 COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, 
                                 COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, 
                                 COUNT(*) AS grandtotal 
                        FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and 
                                                                sourcedomainname like '%' + @Domain + '%' and 
                                                                DATENAME(MM,snhinsertedat) =  @Month 
                GROUP BY elementname 
                ) as t where sev1<>0 and sev2<>0 and sev3<>0 and sev4<>0 and sev5<>0
                order by grandtotal desc
END

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

RiteshShahCommented:
>>no there is no zero in the column<<

which column?


0
Senz79Author Commented:
Hi ritesh i need blank rather than null or 0 and the next query returns no result
0
Senz79Author Commented:
Just need to replace zero with blank
0
RiteshShahCommented:
here you go!!! 0 with blank.

ALTER PROCEDURE [dbo].[spTop20Server] 
        -- Add the parameters for the stored procedure here
        @TechType varchar(50),
        @Domain varchar(50),
        @Month varchar(20)
AS
BEGIN
        SET NOCOUNT ON;

   select top 20 
                elementname,
                case when sev1=0 then '' else sev1 end as sev1, 
                case when sev2=0 then '' else sev2 end as sev2,
                case when sev3=0 then '' else sev3 end as sev3,
                case when sev4=0 then '' else sev4 end as sev4,
                case when sev5=0 then '' else sev5 end as sev5
        from  
   ( 
      SELECT elementname, 
                     COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, 
                     COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, 
                     COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, 
                     COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, 
                     COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, 
                     COUNT(*) AS grandtotal 
            FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and 
                                                    sourcedomainname like '%' + @Domain + '%' and 
                                                    DATENAME(MM,snhinsertedat) =  @Month 
    GROUP BY elementname 
    ) as t order by grandtotal desc
END

Open in new window

0
Senz79Author Commented:

0 is still coming , can it be replaced with blank
0
RiteshShahCommented:
are you sure you have alter your SP? anyway try this one.

ALTER PROCEDURE [dbo].[spTop20Server] 
        -- Add the parameters for the stored procedure here
        @TechType varchar(50),
        @Domain varchar(50),
        @Month varchar(20)
AS
BEGIN
        SET NOCOUNT ON;

   select top 20 
                elementname,
                case when sev1=0 then '' else cast(sev1 as varchar) end as sev1, 
                case when sev2=0 then '' else cast(sev2 as varchar) end as sev2,
                case when sev3=0 then '' else cast(sev3 as varchar) end as sev3,
                case when sev4=0 then '' else cast(sev4 as varchar) end as sev4,
                case when sev5=0 then '' else cast(sev5 as varchar) end as sev5
        from  
   ( 
      SELECT elementname, 
                     COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, 
                     COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, 
                     COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, 
                     COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, 
                     COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, 
                     COUNT(*) AS grandtotal 
            FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and 
                                                    sourcedomainname like '%' + @Domain + '%' and 
                                                    DATENAME(MM,snhinsertedat) =  @Month 
    GROUP BY elementname 
    ) as t order by grandtotal desc
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Senz79Author Commented:
Kindly delete the data in the question on priority.Due to some security issue
0
Senz79Author Commented:
Please replace the attachment at ID:25780262 with the attached data
 res.txt
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.