CODEWITHSUNDEEP
sqloracle-database
mcvkr
mcvkr

Reputation: 3912

Sql - alternative to WITH ... AS

Is there an efficient alternative SQL to this example? I do not want to use WITH ... AS, that is the main criteria

WITH TEMP_TABLE AS (
     SELECT status, COUNT(1) as total FROM XYZ GROUP BY status
    )
SELECT 
         NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'A'),0) AS A_COUNT ,
         NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'B'),0) AS B_COUNT ,
         NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'C'),0) AS C_COUNT ,
         NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'D'),0) AS D_COUNT , 
         NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'E'),0) AS E_COUNT ,
         NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'F'),0) AS F_COUNT  
FROM DUAL;

I read similar questions but they use other ddl stuff, I just want a basic SQL statement.

Upvotes: 0

Views: 3423

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Already given answers are perfect, you should use them. However in order to provide you a general solution (perhaps you have to transform other queries) it would be this one:

SELECT 
     NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'A'),0) AS A_COUNT ,
     NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'B'),0) AS B_COUNT ,
     NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'C'),0) AS C_COUNT ,
     NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'D'),0) AS D_COUNT , 
     NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'E'),0) AS E_COUNT ,
     NVL((SELECT TOTAL  FROM TEMP_TABLE WHERE STATUS = 'F'),0) AS F_COUNT  
FROM 
    (SELECT status, COUNT(1) as total FROM XYZ GROUP BY status) TEMP_TABLE;

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

This is a simple PIVOT query

select *
from (select status from XYZ )
pivot (
   count(*) for status in (
      'A' as a_count, 'B' as b_count, 
      'C' as c_count, 'D' as d_count, 
      'E' as e_count, 'F' as f_count 
));

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Just use conditional aggregation:

SELECT SUM(CASE WHEN STATUS = 'A' THEN 1 ELSE 0 END) AS A_COUNT ,
       SUM(CASE WHEN STATUS = 'B' THEN 1 ELSE 0 END) AS B_COUNT ,
       SUM(CASE WHEN STATUS = 'C' THEN 1 ELSE 0 END) AS C_COUNT ,
       SUM(CASE WHEN STATUS = 'D' THEN 1 ELSE 0 END) AS D_COUNT ,
       SUM(CASE WHEN STATUS = 'E' THEN 1 ELSE 0 END) AS E_COUNT ,
       SUM(CASE WHEN STATUS = 'F' THEN 1 ELSE 0 END) AS F_COUNT 
FROM XYZ ;

I should point out that putting the values on separate rows is even easier:

select status, count(*)
from xyz
group by status;

Upvotes: 4

Related Questions