CODEWITHSUNDEEP
sqloracle-databaseselectcount
RNJ
RNJ

Reputation: 15572

count and distinct over multiple columns

I have a database table containing two costs. I want to find the distinct costs over these two columns. I also want to find the count that these costs appear. The table may look like

|id|cost1|cost2|
|1 |50   |60   |
|2 |20   |50   |
|3 |50   |70   |
|4 |20   |30   |
|5 |50   |60   |

In this case I want a result that is distinct over both columns and count the number of times that appears. So the result I would like is

|distinctCost|count|
|20          |2    |
|30          |1    |
|50          |4    |
|60          |2    |
|70          |1    |

and ideally ordered

|disctinCost1|count|
|50          |4    |
|60          |2    |
|20          |2    |
|70          |1    |
|30          |1    |

I can get the distinct over two columns by doing something like

select DISTINCT c FROM (SELECT cost1 AS c FROM my_costs UNION SELECT cost2 AS c FROM my_costs);

and I can get the count for each column by doing 

select cost1, count(*)
from my_costs 
group by cost1
order by count(*) desc;

My problem is how can I get the count for both columns? I am stuck on how to do the count over each individual column and then add it up.

Any pointers would be appreciated.

I am using Oracle DB.

Thanks

Upvotes: 0

Views: 3375

Answers (2)

planben
planben

Reputation: 700

You can use the unpivot statement :

select * 
from 
(
  SELECT cost , count(*) as num_of_costs
  FROM   my_costs 
  UNPIVOT 
  (
     cost                          
     FOR cost_num IN  (cost1,cost2) 
  )
 group by cost
) 
order by num_of_costs desc;

Upvotes: 0

podiluska
podiluska

Reputation: 51514

By combining your two queries..

select cost, count(*)
from
(
    SELECT id, cost1 AS cost FROM my_costs 
    UNION ALL
    SELECT id, cost2 AS c FROM my_costs
) v
group by cost
order by count(*) desc;

(If when a row has cost1 and cost2 equal, you want to count it once not twice, change the union all to a union)

Upvotes: 2

Related Questions