Simplify MS SQL Statements

Question

I read a great post on JOINS, What is the difference between "INNER JOIN" and "OUTER JOIN"? but I don't think it helps my issue.

I would like to combine two select statements into a single select statement AND, make it efficient for any databases to query.

Here is what I have

SELECT
(SELECT COUNT(*) 
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%') AS 'Windows-SEP-11',
(SELECT COUNT(*) 
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%') AS 'Windows-SEP-12',
(SELECT COUNT(*) 
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%') AS 'Mac-SEP-11',
(SELECT COUNT(*)
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%')  AS 'Mac-SEP-12'

How to shrink this SQL statement? Even if I use Inner Joins (which I'm working on), this statement is quite processor intensive (not to mention it seems really inefficient)

Answer 1

You can do this using Conditional Aggregate.

Also use proper INNER JOIN syntax to Join two tables instead of old style comma separated join. Keep the filters in Where clause

SELECT Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
             END) AS 'Windows-SEP-11',
       Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
             END) AS 'Windows-SEP-12',
       Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
             END) AS 'Mac-SEP-11',
       Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
             END) AS 'Mac-SEP-12'
FROM   dbo.sem_computer
       INNER JOIN [dbo].[V_SEM_COMPUTER]
               ON [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.SEM_AGENT
               ON SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
       INNER JOIN dbo.SEM_CLIENT
               ON dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.IDENTITY_MAP
               ON dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
       INNER JOIN dbo.PATTERN
               ON dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
WHERE  Name LIKE '%Staten Island%'