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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
RiteshShahConnect With a Mentor Commented:
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.