CODEWITHSUNDEEP
mysqlsql
Jason Barajas
Jason Barajas

Reputation: 11

In SQL, how can I write a query to determine the percentage of a certain value in a column after I have queried its COUNT?

-- Here's my code:

SELECT
COUNT(Action_taken)
FROM
Friend_requests
WHERE action_taken = “Accepted”

-- How can I write a second query to determine the percentage of values that equal "accepted"?

Upvotes: 1

Views: 213

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The simplest method is conditional aggregation. In MySQL, you can express this as:

select avg(action = 'accepted')
from friend_requests;

This works because MySQL treats boolean values as 1 for true and 0 for false. So the average is the ratio of true values over all the values.

Note: This will ignore NULL values for action. That can trivially be handled using the NULL-safe comparison operator:

select avg(action <=> 'accepted')
from friend_requests;

Upvotes: 2

Nguyễn Văn Quyền
Nguyễn Văn Quyền

Reputation: 350

As @Gordon Linoff's answer and @Strawberry's comment, The ful query for me kinda like:

select round(avg(action = 'accepted') * 100, x)
from friend_requests
where action_taken is not null
  • x is how many numbers after decimal

Upvotes: 0

Ryan
Ryan

Reputation: 6045

select
    (select count(1) from friend_requests where action = "accepted")
    /
    (select count(1) from friend_requests)

Upvotes: 0

Related Questions