CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
RyanB
RyanB

Reputation: 69

Distinct values despite a column in a case expression creating non distinct values

I want to get one line per APP_ID but I am selecting Operating_System in a case statement which means 3 rows are returned for the same APP_ID.

SELECT DISTINCT [APP_ID], [APP_NAME], [BATCH_ID], [Start_Date], 
       CASE 
         WHEN [Operating_System] LIKE '%Windows%' 
            then COUNT([HOSTNAME])  
         ELSE '' 
       END AS 'Windows',  
       CASE 
         WHEN [server_pam_Operating_System] LIKE '%Linux%' 
           then COUNT([HOSTNAME]) 
         ELSE '' 
       END AS 'Linux' 
FROM [dbo].[table a]
WHERE [APP_ID] = 'APP-99'
GROUP BY [APP_ID], [APP_NAME], [BATCH_ID], [Start_Date]

Actual results:

APP_ID  APP_NAME BATCH_ID   Start_Date  Windows Linux
APP-99  APS to define   to define   0   49
APP-99  APS to define   to define   3   0
APP-99  APS to define   to define   7   0

Expected results:

APP_ID  APP_NAME BATCH_ID   Start_Date  Windows Linux
APP-99  APS to define   to define   10  49

Upvotes: 1

Views: 53

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

no need distinct and use aggregation before case 

    SELECT  [APP_ID], [APP_NAME], [BATCH_ID], [Start_Date],         
    sum(CASE WHEN [Operating_System] LIKE '%Windows%' then 1  
    ELSE 0 END) AS Windows,
    sum(CASE WHEN [server_pam_Operating_System] LIKE '%Linux%' 
    then 1 ELSE 0) END AS Linux    
      FROM [dbo].[table a]
      WHERE [APP_ID] = 'APP-99'
      GROUP BY [APP_ID], [APP_NAME], [BATCH_ID], [Start_Date]

Upvotes: 2

Related Questions