CODEWITHSUNDEEP
sqlsql-servert-sql
Rohini Mathur
Rohini Mathur

Reputation: 441

Output of Multiple Select Statement In Single Row

I have multiple query which is providing count based on different where condition.

Will it be possible to bring all result in single Rows.

for eg:

Query1: 
SELECT COUNT(COL25) ASSURED, FROM TAB1 WHERE COL1= 'ALPHA' AND COL2='ROLE'

Query2:
SELECT COUNT(COL25) RELEASE FROM TAB1 WHERE COL3 in('BEETA','X','Y') AND COLSTATUS='ABC'

The result for 1st query is ASSURED = 100
and 2nd Query is RELEASE = 5000

i am trying to display output as

ASSURED  | RELEASE
100      | 5000

Upvotes: 1

Views: 2568

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Use conditional aggregation:

SELECT SUM(CASE WHEN COL1 = 'ALPHA' AND COL2 = 'ROLE' THEN 1 ELSE 0 END)  as ASSURED,
       SUM(CASE WHEN COL3 = 'BEETA' AND COLSTATUS = 'ABC' THEN 1 ELSE 0 END) as RELEASE
FROM TAB1;

EDIT:

If you actually need to count non-NULL values, you can be explicit (my preference):

SELECT SUM(CASE WHEN COL1 = 'ALPHA' AND COL2 = 'ROLE' AND col25 IS NOT NULL THEN 1 ELSE 0 END)  as ASSURED,
       SUM(CASE WHEN COL3 = 'BEETA' AND COLSTATUS = 'ABC' AND col25 IS NOT NULL THEN 1 ELSE 0 END) as RELEASE
FROM TAB1;

Or be a bit more implicit:

SELECT COUNT(CASE WHEN COL1 = 'ALPHA' AND COL2 = 'ROLE' THEN col25 END) as ASSURED,
       COUNT(CASE WHEN COL3 = 'BEETA' AND COLSTATUS = 'ABC' THEN col25 END) as RELEASE
FROM TAB1;

Upvotes: 4

Rokuto
Rokuto

Reputation: 814

You can also use joins, but @Gordon Linoff answer is cleaner and way shorter than this.

SELECT first.ASSURED, second.RELEASE
FROM
    (SELECT
         COUNT(COL25) ASSURED
    FROM TAB1 
    WHERE COL1= 'ALPHA' AND COL2='ROLE') AS first
INNER JOIN (SELECT 
                 COUNT(COL25) RELEASE 
             FROM TAB1 
             WHERE COL3 in('BEETA','X','Y') 
             AND COLSTATUS='ABC') AS second
         ON 1=1

Upvotes: 0

Related Questions