replace some value in sql with Null and perform count function in sql server while performing group-CodePudding

there is LAT_LONG field which I want to count for each ID, the LAT_LONG contain some string value as 'null/null' which I want to replace with NULL, and than perform the group by operation

 select ID, COUNT(LAT_LONG) as LAT_LONG_CAT from mytable group by ID

How to replace 'null/null' string with Null in LAT_LONG field

CodePudding user response：

In SQL Server you can simply use NULLIF function like so:

SELECT lat_long, NULLIF(lat_long, 'null/null') AS lat_long_parsed FROM mytable

Example on dbfiddle

So in the end your query would be:

SELECT ID
     , COUNT(NULLIF(lat_long, 'null/null')) AS LAT_LONG_CAT
  FROM mytable
 GROUP BY id

CodePudding user response：

Use a CASE expression:

SELECT ID,
       COUNT(CASE WHEN LAT_LONG <> 'null/null'
                  THEN LAT_LONG END) AS LAT_LONG_CNT
FROM mytable
GROUP BY ID;

The above CASE expression will treat a value of null/null as NULL, otherwise it counts the LAT_LONG field directly.