SQL: count when value in one column appears in same row as another value from different column [duplicate]

Question

Using the following data set, I want to find the average rate, while counting how many times the values in Condition and Intervention are in the same row together.

This is an example of the table I'm using:

Condition | Intervention | Rate of Subjects Affected
-----------------------------------------------------
Anxiety   | Drug         | 0.02
Anxiety   | Behavioral   | 0.05
Anxiety   | Drug         | 0.001
Depression| Other        | 0.7
Depression| Other        | 0.8

And I want to create a query where it will only count for the Anxiety/Drug pair once and find the average of the two rates it collected (.02 and .001)

This is the result I'm hoping for:

Condition | Intervention | Rate of Subjects Affected
-----------------------------------------------------
Anxiety   | Drug         | 0.0105
Anxiety   | Behavioral   | 0.05
Depression| Other        | 0.75

Please let me know if you have any ideas for this!

Yes, I have the table in sql and in a pandas dataframe. Either answers would work — lex449
– lex449, Commented May 3, 2019 at 20:07

Arulkumar · Accepted Answer · 2019-05-03 19:57:52Z

2

Using AVG() aggregate function, you can get the expected result

SELECT Condition, Intervention, AVG(`Rate of Subjects Affected`) AS Rate
FROM TableName
GROUP BY Condition, Intervention

answered May 3, 2019 at 19:57

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: count when value in one column appears in same row as another value from different column [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related