locked
How to avoid the similer entries in the colum list in sql server ? RRS feed

  • Question

  • Hi am sharing one sql query and o/p:

    select distinct  case 
              when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
              when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
              when LastExecutionResult = '17023' then 'User cancelled installation'
              when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
              when LastExecutionResult = '17028' then 'Office 2013 SP1 already Exist' 
                     else 'Application will be Installed once machine is Online' end as Comment
                ,  Count(ResourceID) MachineCount
      from V_ClientAdvertisementStatus
      where  AdvertisementID='000000'
      Group by  LastStatusMessageIDName, LastExecutionResult,

    The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)

    Comment  Machine Name
    Application will be Installed once machine is Online 4
    Application will be Installed once machine is Online 12
    Application will be Installed once machine is Online 42
    Application will be Installed once machine is Online 120
    Machine Does not have Office 2013 25
    User cancelled installation 32
    Application failed due to low disk space 41
    Office 2013 SP1 already Exist 60

    i need o/p like below:in single line

    Application will be Installed once machine is Online 178
    Machine Does not have Office 2013 25
    User cancelled installation 32
    Application failed due to low disk space 41
    Office 2013 SP1 already Exist 60

    • Edited by Ychinnari Wednesday, September 16, 2015 12:06 PM
    Wednesday, September 16, 2015 12:04 PM

Answers

  • See if this helps you

    with cte
    as
    (
    select distinct  case 
              when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
              when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
              when LastExecutionResult = '17023' then 'User cancelled installation'
              when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
              when LastExecutionResult = '17028' then 'Office 2013 SP1 already Exist' 
                     else 'Application will be Installed once machine is Online' end as Comment
                ,  Count(ResourceID) MachineCount
      from V_ClientAdvertisementStatus
      where  AdvertisementID='000000'
      Group by  LastStatusMessageIDName, LastExecutionResult
      ) SELECT Comment,COUNT(*) cnt FROM cte GROUP BY Comment


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Milan Das Thursday, September 17, 2015 4:57 AM
    • Marked as answer by Kalman Toth Monday, September 28, 2015 7:31 PM
    Wednesday, September 16, 2015 12:32 PM

All replies

  • See if this helps you

    with cte
    as
    (
    select distinct  case 
              when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
              when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
              when LastExecutionResult = '17023' then 'User cancelled installation'
              when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
              when LastExecutionResult = '17028' then 'Office 2013 SP1 already Exist' 
                     else 'Application will be Installed once machine is Online' end as Comment
                ,  Count(ResourceID) MachineCount
      from V_ClientAdvertisementStatus
      where  AdvertisementID='000000'
      Group by  LastStatusMessageIDName, LastExecutionResult
      ) SELECT Comment,COUNT(*) cnt FROM cte GROUP BY Comment


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Milan Das Thursday, September 17, 2015 4:57 AM
    • Marked as answer by Kalman Toth Monday, September 28, 2015 7:31 PM
    Wednesday, September 16, 2015 12:32 PM
  •  

    SELECT Comment, SUM(MachineCount) AS Final_count FROM ( select distinct case when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully' when LastExecutionResult = '2013' then 'Machine Does not have Office 2013' when LastExecutionResult = '17023' then 'User cancelled installation' when LastExecutionResult = '17302' then 'Application failed due to low disk space.' when LastExecutionResult = '17028' then 'Office 2013 SP1 already Exist' else 'Application will be Installed once machine is Online' end as Comment , Count(ResourceID) MachineCount from V_ClientAdvertisementStatus where AdvertisementID='000000' Group by LastStatusMessageIDName, LastExecutionResult ) i GROUP BY COMMENT


    • Proposed as answer by Raja B Wednesday, September 23, 2015 12:56 PM
    Wednesday, September 16, 2015 1:46 PM