CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Jayam Koko
Jayam Koko

Reputation: 49

Using countif in Oracle sql to calculate for each row

Can i know is there any sql statment like in Excel countif to count high, medium and low which is based on condition.

For instance, the table below shows on using countif in high we can see High how much, low how much and medium how much for each row

ID   Name     Value1     Value2     Value3    ValueN   High   Medium  Low
---  -------  ---------  ---------  --------  -------  -----  ------  ------
1    A        High       High       Low       Medium   2      1       1 
2    AB       Low        Medium     Low       High     1      1       2
3    ABC      High       Low        Low       High     2      0       2

Upvotes: 2

Views: 1766

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

You can use first UNPIVOT data and then do conditional aggregation:

SELECT ID, Name,
       COUNT(CASE WHEN val = 'High' THEN 1 END)   AS High,
       COUNT(CASE WHEN val = 'Medium' THEN 1 END) AS Medium,
       COUNT(CASE WHEN val = 'Low' THEN 1 END)    AS Low
FROM tab
UNPIVOT( val FOR col_name IN (Value1, Value2, Value3, ValueN)) unpvt
GROUP BY ID, Name

LiveDemo

To handle more column just add them to:

val FOR col_name IN (Value1, Value2, Value3, ValueN)

And final query with all columns from original tab:

SELECT t.*, sub.High, sub.Medium, sub.Low
FROM tab t
JOIN (SELECT ID, Name, 
         COUNT(CASE WHEN val = 'High' THEN 1 END)   AS High,
         COUNT(CASE WHEN val = 'Medium' THEN 1 END) AS Medium,
         COUNT(CASE WHEN val = 'Low' THEN 1 END)    AS Low
      FROM tab
      UNPIVOT( val FOR col_name IN (Value1, Value2, Value3, ValueN)) unpvt
      GROUP BY ID, Name) sub
  ON t.ID = sub.ID

LiveDemo2

Output:

╔════╦══════╦════════╦════════╦════════╦════════╦══════╦════════╦═════╗
║ ID ║ Name ║ Value1 ║ Value2 ║ Value3 ║ ValueN ║ High ║ Medium ║ Low ║
╠════╬══════╬════════╬════════╬════════╬════════╬══════╬════════╬═════╣
║  1 ║ A    ║ High   ║ High   ║ Low    ║ Medium ║    2 ║      1 ║   1 ║
║  2 ║ AB   ║ Low    ║ Medium ║ Low    ║ High   ║    1 ║      1 ║   2 ║
║  3 ║ ABC  ║ High   ║ Low    ║ Low    ║ High   ║    2 ║      0 ║   2 ║
╚════╩══════╩════════╩════════╩════════╩════════╩══════╩════════╩═════╝

Upvotes: 2

Related Questions