Reputation: 15572
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
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
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