0

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!

piRSquared
  • 285,575
  • 57
  • 475
  • 624
lex449
  • 139
  • 1
  • 12

1 Answers1

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
Arulkumar
  • 12,966
  • 14
  • 47
  • 68