SQL Request : Count with group by and total to make a percentage on the same line

user_1398300 0
Viewed 49

I'm currently blocked on a simple request... :

I have this database :

order | couple | first | second
-------------------------------
  1   |  A-B   |   A   |   B
  2   |  A-D   |   A   |   D
  3   |  A-C   |   A   |   C
  4   |  A-B   |   A   |   B
  5   |  Y-Z   |   Y   |   Z

I would like to obtain, on one line :

order | count | total | percentage
-------------------------------
A-B   |  2   |   4    |    50%
A-D   |  1   |   4    |    25%
A-C   |  1   |   4    |    25%
Y-Z   |  1   |   1    |    100%

I'm ok to find the first part : order | count... But Impossible to join it with the total and percentage (instead using subqueries...) : I've got a lot of different error when I try to make the join.

My request for the first part :

SELECT couple,count(couple) 
FROM DATA d1 
WHERE exists (
  SELECT * 
  FROM DATA d2 
  WHERE d1.first = d2.first) 
GROUP BY d1.couple;

My request for the second part :

SELECT first, count(first) 
FROM DATA 
GROUP BY first;

Someone gots the tips ?

percentage subquery postgresql oracle sql
1 Answers

I think you want:

select couple, count(*),
       count(*) over (partition by first) as total,
       (count(*) * 1.0 / sum(count(*)) over (partition by first) ) as ratio
from t
group by couple, first;
user_11440350
Related