SQL Case Statement COUNT

Question

I need to do a case statement, group the results and count the results in my SQL query. I have done this so far but not quite sure how to get it to work properly:

SELECT 'OS' = 
    CASE
        WHEN OS.Info LIKE 'AIX %' THEN 'Unix'
        WHEN OS.Info LIKE 'LINUX%' THEN 'Unix'
    END, COUNT( 'OS' )
FROM Asset A (NOLOCK)
LEFT JOIN Asset_IP AIP (NOLOCK) ON A.AssetID = AIP.AssetID AND AIP.LastUpdateDate = (SELECT MAX( Asset_IP.LastUpdateDate ) FROM Asset_IP (NOLOCK) WHERE A.AssetID = Asset_IP.AssetID)
LEFT JOIN Asset_IP_OS AIPOS (NOLOCK) ON AIP.AssetIPID = AIPOS.AssetIPID
LEFT JOIN OSNav OS (NOLOCK) ON AIPOS.OSNavID = OS.OSNavID
GROUP BY OS.Info
ORDER BY OS.Info

The OS may come up as AIX 7.0 or AIX 7.2 or Linux 2.3 or Windows 7 or Solaris 10. All the Unix based OS must be under Unix with a count, etc... I would like it to show something like this:

OS      Count
Unix    200
Windows 1200

Answer 1

You need to use CASE statement in Group By or use derived table to use the Alias name OS in group by . Since we have removed OS.Info in group by, we cannot use OS.Info in order by. Use alias name OS in Order by

SELECT OS = CASE
              WHEN OS.Info LIKE 'AIX %'
                    OR OS.Info LIKE 'LINUX%' THEN 'Unix'
              ELSE 'Others' --default
            END,
       Count(OS.Info)
FROM   Asset A (NOLOCK)
       LEFT JOIN Asset_IP AIP (NOLOCK)
              ON A.AssetID = AIP.AssetID
                 AND AIP.LastUpdateDate = (SELECT Max(Asset_IP.LastUpdateDate)
                                           FROM   Asset_IP (NOLOCK)
                                           WHERE  A.AssetID = Asset_IP.AssetID)
       LEFT JOIN Asset_IP_OS AIPOS (NOLOCK)
              ON AIP.AssetIPID = AIPOS.AssetIPID
       LEFT JOIN OSNav OS (NOLOCK)
              ON AIPOS.OSNavID = OS.OSNavID
GROUP  BY CASE
            WHEN OS.Info LIKE 'AIX %'
                  OR OS.Info LIKE 'LINUX%' THEN 'Unix'
            ELSE 'Others'
          END
ORDER  BY OS 

Note : I have added else statement to avoid NULL's when expressions are not satisfied..