Combine 3 SQL queries in to one-CodePudding

I have three queries and I want them to display in one table with three columns. I researched but could not able to get it done correctly. Any help would be highly appriciated.

below are my three queries.

select count(NodeID) AS Total_Ports from UDT_Port where IsMonitored = 'True'
select count(NodeID) AS Free_Ports from UDT_Port where IsMonitored = 'True' and operationalStatus NOT Like '%1%'
Select (select count(NodeID) AS Total_Ports from ( select * from UDT_Port where IsMonitored = 'True' ) a )- (select count(NodeID) AS Free_Ports from ( select * from UDT_Port where IsMonitored = 'True' and operationalStatus NOT Like '%1%' ) b) as Used_Ports

CodePudding user response：

We can use a single pass query along with conditional aggregation:

SELECT
    COUNT(CASE WHEN IsMonitored = 'True' THEN 1 END) AS Total_Ports,
    COUNT(CASE WHEN IsMonitored = 'True' AND operationalStatus NOT LIKE '%1%'
               THEN 1 END) AS Free_Ports,
    COUNT(CASE WHEN IsMonitored = 'True' THEN 1 END) -
        COUNT(CASE WHEN IsMonitored = 'True' AND
                        operationalStatus NOT LIKE '%1%'
                   THEN 1 END) AS Used_Ports
FROM UDT_Port;