CODEWITHSUNDEEP
sqlpostgresql
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5236

How to know arithmetic mean of two count values

I have table answers where I store information.

| EMPLOYEE | QUESTION_ID | QUESTION_TEXT          | SELECTED_OPTION_ID | SELECTED_OPTION_TEXT |
|----------|-------------|------------------------|--------------------|----------------------|
| Mark     | 1           | Do you like soup?      | 1                  | Yes                  |
| Kate     | 1           | Do you like soup?      | 1                  | Yes                  |
| Jone     | 1           | Do you like soup?      | 2                  | No                   |
| Kim      | 1           | Do you like soup?      | 3                  | I don't know         |
| Alex     | 1           | Do you like soup?      | 2                  | No                   |
| Bond     | 1           | Do you like soup?      | 1                  | Yes                  |
| Ford     | 1           | Do you like soup?      | 3                  | I don't know         |
| Mark     | 2           | Do you like ice cream? | 2                  | No                   |
| Kate     | 2           | Do you like ice cream? | 1                  | Yes                  |
| Jone     | 2           | Do you like ice cream? | 1                  | Yes                  |
| Kim      | 2           | Do you like ice cream? | 1                  | Yes                  |
| Alex     | 2           | Do you like ice cream? | 2                  | No                   |
| Bond     | 2           | Do you like ice cream? | 1                  | Yes                  |
| Ford     | 2           | Do you like ice cream? | 3                  | I don't know         |

Formulas:

value_1 = (Number of users who answered "No" or "I don't know" to the first question) / (The total number of people who answered to the first question)

value_2 = (Number of users who answered "No" or "I don't know" to the second question) / (The total number of people who answered to the first question)

I can separately find the values according to the above formulas. For example value_1:

select
    count(*)
from
    answers
where
    question_id = 1
    and (
        selected_option_id in (2, 3)
        or 
        selected_option_text in ('No', 'I don\'t know')
    )

My question is how to arithmetic mean of these 2 values correctly by one sql query?

In other words I need to find average value: enter image description here

Upvotes: 1

Views: 50

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I'm pretty sure you want conditional aggregation. I suspect you want:

select question_id,
       count(*) filter (where selected_option_id in (2, 3)) as num_2_3,
       avg( selected_option_id in (2, 3)::int ) as ratio_2_3
from answers
group by question_id;

For each question, this provides the number of answers that are 2 or 3 and the ratio of those answers to all answers.

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Are you looking for something like below-

SELECT 
SUM(CASE  WHEN QUESTION_ID = 1 AND SELECTED_OPTION_TEXT  <> 'Yes' THEN 1 ELSE 0 END)
/
SUM(CASE  WHEN QUESTION_ID = 1 THEN 1 ELSE 0 END) value_1 ,
SUM(CASE  WHEN QUESTION_ID = 2 AND SELECTED_OPTION_TEXT  <> 'Yes' THEN 1 ELSE 0 END)
/
SUM(CASE  WHEN QUESTION_ID = 2 THEN 1 ELSE 0 END) value_2
FROM answers

For getting average, please use the below script-

SELECT (A.value_1+A.value_2)/2.0 
FROM
(
    SELECT 
    SUM(CASE  WHEN QUESTION_ID = 1 AND SELECTED_OPTION_TEXT  <> 'Yes' THEN 1 ELSE 0 END)*1.0
    /
    SUM(CASE  WHEN QUESTION_ID = 1 THEN 1 ELSE 0 END)*1.0 value_1 ,
    SUM(CASE  WHEN QUESTION_ID = 2 AND SELECTED_OPTION_TEXT  <> 'Yes' THEN 1 ELSE 0 END)*1.0
    /
    SUM(CASE  WHEN QUESTION_ID = 2 THEN 1 ELSE 0 END)*1.0 value_2
    FROM answers
)A

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a condition sum 

select  (sum( case when QUESTION_ID = 1 AND  
        SELECTED_OPTION_ID in ( 2,3) THEN 1 else 0 end )::float /  
      sum( case when QUESTION_ID = 1 then 1 else 0 end)::float )*100 first_question_rate,
    (sum( case when QUESTION_ID = 2 AND  
        SELECTED_OPTION_ID in ( 2,3) THEN 1 else 0 end )::float /  
      sum( case when QUESTION_ID = 2 then 1 else 0 end)::float)*100 second_question_rate,
    (( sum( case when QUESTION_ID = 1 AND  SELECTED_OPTION_ID in ( 2,3) THEN 1 else 0 end )::float /  
      sum( case when QUESTION_ID = 1 then 1 else 0 end)::float +
      sum( case when QUESTION_ID = 2 AND  SELECTED_OPTION_ID in ( 2,3) THEN 1 else 0 end )::float /  
      sum( case when QUESTION_ID = 2 then 1 else 0 end) ::float)/2)*100 avg 
from answer

Upvotes: 1

Related Questions