0

I have 2 different tables called observations and intervals.

observations:

id,

type,

date

1 recess 03.05.2011 17:00

2 recess 03.06.2011 12:00

intervals:

id,

observation id,

value

1 1 5

2 1 8

3 2 4

4 2 4

I want a view that will display:

observation_id

percent_positive ((count where value = 5)/(total number of observations))

1 .5

2 0

I know

Select observation_id, Count(*) from intervals where value = 5 Group by 
observation_id

will give me:

1 1

1 0

and

Select observation_id, Count(*) from intervals Group by 
observation_id

will give me:

1 2

2 2

So how do I combine these to create a view with the percent_positive variable I'm looking for?

Austin Hardesty
  • 27
  • 6

3 Answers3

0

You can use joins to fetch data from two tables having a common column field .

For more ,please read it in detail Multiple values from multiple Tables

tom
  • 3,720
  • 5
  • 26
  • 48
0
SELECT 
    i.observation_id,
    SUM(IF(i.value=5,1,0)) / counts.num as 'percent_positive'
FROM intervals i
inner join (
    select observation_id, count(1) as num from intervals group by observation_id
) counts on counts.observation_id = i.observation_id
group by i.observation_id
order by i.observation_id
;

That oughta get you close, can't actually run to test at the moment. I'm not sure about the significance of the value 5 meaning positive, so the i.value=5 test might need to be modified to do what you want. This will only include observation IDs with intervals that refer to them; if you want ALL observations then you'll need to join that table (select from that table and left join the others, to be precise). Of course the percentage for those IDs will be 0 divided by 0 anyway...

A C
  • 705
  • 6
  • 9
0

This gave me your desired result. Not proficient enough in SQL to determine if this is the optimal way of solving the issue though.

SELECT
  observation_id as obs,
  (SELECT COUNT(*) FROM intervals WHERE observation_id = obs AND value = 5)/(SELECT COUNT(*) FROM INTERVALS WHERE observation_id = obs) as percent
FROM observation
JOIN intervals ON observation.id = intervals.observation_id
GROUP BY observation_id;
jared
  • 473
  • 3
  • 16