CODEWITHSUNDEEP
sqloracle-databasecount
user3619807
user3619807

Reputation: 13

Count from several columns Oracle Sql

I have a table that contain the following:

Fruit_id     Fruit1            Fruit2
-------------------------------------
1            Apple             Orange
2            Orange            Orange
3            Orange            Orange
4            Banana            Banana
5            Apple             Orange

I would like to count the total number for each fruit so that the output is something like

Fruit            Frequency
---------------------------
Apple            2
Banana           2
Orange           6

I have tried 

select distinct Fruit1, count(Fruit1 Fruit2) from Fruits group by Fruit1 order by count(Fruit1 Fruit2);

I also tried:

select distinct Fruit1, count(Fruit1 || Fruit2) from Fruits group by Fruit1 order by count(Fruit1 Fruit2);

I'm new to oracle sql so please understand my ignorance

Upvotes: 0

Views: 124

Answers (2)

spencer7593
spencer7593

Reputation: 108370

You could get the count for each distinct fruit value in the Fruit1 and Fruit2 columns, and then add those together with a SUM aggregate.

The "trick" is to use an inline view that concatenates the two results together with a UNION ALL set operator.

SELECT f.fruit, SUM(f.cnt) AS cnt
  FROM ( SELECT d.Fruit1 AS fruit, COUNT(1) AS cnt FROM Fruits d GROUP BY d.Fruit1 
          UNION ALL
         SELECT e.Fruit2 AS fruit, COUNT(1) AS cnt FROM Fruits e GROUP BY e.Fruit2
       ) f
 GROUP BY f.fruit
 ORDER BY f.fruit

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

You could use the following:

select fruits, count(*) as freq
  from (select fruit1 as fruits
          from tbl
        union all
        select fruit2 as fruits
          from tbl)
 group by fruits

Upvotes: 1

Related Questions