I have a table connection_master in that table column name status_conn have 1 value for ON conn. and 2 for OFF conn. now I want to get only counts of ON and OFF connections in one query

I want output like this

on_counts   off_counts
110         55

CodePudding user response：

Use conditional aggregation:

SELECT
    COUNT(CASE WHEN status_conn = 1 THEN 1 END) AS on_counts,
    COUNT(CASE WHEN status_conn = 2 THEN 1 END) AS off_counts
FROM connection_master;

CodePudding user response：

SELECT COUNT(status_conn) AS CONN_ON,COUNT(status_conn) AS CONN_OFF FROM connection_master where substr(status_conn,1,1) IN (1,2)

CodePudding user response：

You can use a PIVOT:

SELECT *
FROM   connection_master
PIVOT  (COUNT(*) FOR status_conn IN (1 AS on_count, 2 AS off_count));

Which, for the sample data:

CREATE TABLE connection_master (status_conn) AS
SELECT 1 FROM DUAL CONNECT BY LEVEL <= 110 UNION ALL
SELECT 2 FROM DUAL CONNECT BY LEVEL <= 55;

Outputs:

fiddle