CODEWITHSUNDEEP
sqlsql-server
Glowie
Glowie

Reputation: 2309

Display data if Count(distinct) is 0

SQL Server 2008

SQL Query displays IP address if present 

SELECT [IP_ADDR1_TEXT], COUNT(distinct [IP_ADDR1_TEXT]) as Instances
FROM sem_computer, [dbo].[V_SEM_COMPUTER]
WHERE sem_computer.COMPUTER_ID = [dbo].[V_SEM_COMPUTER].COMPUTER_ID
AND [IP_ADDR1_TEXT] = '10.10.10.10'
GROUP BY [IP_ADDR1_TEXT]

Output

IP_ADDR1_TEXT   Instances
10.10.10.10         1

If IP address count is 0, the output is null.

How do I modify the query so that Instances outputs 0, next to the non-existant IP address, i.e.

SELECT [IP_ADDR1_TEXT], COUNT(distinct [IP_ADDR1_TEXT]) as Instances
FROM sem_computer, [dbo].[V_SEM_COMPUTER]
WHERE sem_computer.COMPUTER_ID = [dbo].[V_SEM_COMPUTER].COMPUTER_ID
AND [IP_ADDR1_TEXT] = '10.10.10.11'
GROUP BY [IP_ADDR1_TEXT]

Should output

IP_ADDR1_TEXT   Instances
10.10.10.11         0

EDIT

Screenshot of VIEW

enter image description here

IP_ADDR1_TEXT is an ALIAS to

Column CAST(CASE WHEN IP_ADDR1 < 0 THEN 0xFFFFFFFF + IP_ADDR1 ELSE IP_ADDR1 END / 256 / 256 / 256 & 0xFF AS VARCHAR) + '.' + CAST(CASE WHEN IP_ADDR1 < 0 THEN 0xFFFFFFFF + IP_ADDR1 ELSE IP_ADDR1 END / 256 / 256 & 0xFF AS VARCHAR) + '.' + CAST(CASE WHEN IP_ADDR1 < 0 THEN 0xFFFFFFFF + IP_ADDR1 ELSE IP_ADDR1 END / 256 & 0xFF AS VARCHAR) + '.' + CAST(CASE WHEN IP_ADDR1 < 0 THEN 0xFFFFFFFF + IP_ADDR1 ELSE IP_ADDR1 END & 0xFF AS VARCHAR)

Upvotes: 0

Views: 149

Answers (2)

Max
Max

Reputation: 7080

SELECT [IP_ADDR1_TEXT], COALESCE(COUNT(distinct [IP_ADDR1_TEXT]),0) as Instances
FROM sem_computer LEFT JOIN [dbo].[V_SEM_COMPUTER]
  ON sem_computer.COMPUTER_ID = [dbo].[V_SEM_COMPUTER].COMPUTER_ID
AND [IP_ADDR1_TEXT] = '10.10.10.11'
GROUP BY [IP_ADDR1_TEXT]

Upvotes: 1

Gary Brunton
Gary Brunton

Reputation: 1950

Maybe try this:

SELECT [IP_ADDR1_TEXT], COUNT(distinct [IP_ADDR1_TEXT]) as Instances
FROM sem_computer
Left JOin [dbo].[V_SEM_COMPUTER] On sem_computer.COMPUTER_ID = [dbo].[V_SEM_COMPUTER].COMPUTER_ID
WHERE [IP_ADDR1_TEXT] = '10.10.10.10'
GROUP BY [IP_ADDR1_TEXT]

Upvotes: 0

Related Questions