CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
yalestar
yalestar

Reputation: 9564

Counting and aggregating by two rows

Say I have a table like the following :

id  | key           | user_id | value
---------------------------------------
8   | bp_diastolic  | 1       | 93.0
7   | bp_systolic   | 1       | 128.0
20  | bp_systolic   | 2       | 108.0
21  | bp_diastolic  | 2       | 76.0
35  | bp_diastolic  | 3       | 113.0
34  | bp_systolic   | 3       | 101.0
48  | bp_systolic   | 4       | 115.0
49  | bp_diastolic  | 4       | 77.0
62  | bp_systolic   | 5       | 143.0
63  | bp_diastolic  | 5       | 104.0
77  | bp_diastolic  | 6       | 108.0
76  | bp_systolic   | 6       | 144.0
90  | bp_systolic   | 7       | 122.0
91  | bp_diastolic  | 7       | 81.0
105 | bp_diastolic  | 8       | 110.0
104 | bp_systolic   | 8       | 146.0
118 | bp_systolic   | 9       | 141.0
119 | bp_diastolic  | 9       | 72.0
132 | bp_systolic   | 10      | 120.0
133 | bp_diastolic  | 10      | 107.0

How would I count all of the user_ids with bp_systolic below 120 and bp_diastolic less than 80?

I can't figure out how to do that since the condition applies to two different rows for a single user. In fact, I lack even the words to form a relevant web search for what I want.

Upvotes: 1

Views: 44

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656421

If we know that there is at most one entry for each (user_id, key) - which could be guaranteed by a constraint UNIQUE (user_id, key), this should be fastest::

SELECT user_id
FROM   tbl
WHERE  key = 'bp_systolic' AND value < 120 OR
       key = 'bp_diastolic' AND value < 80
GROUP  BY 1
HAVING count(*) = 2;

The HAVING clause is only needed if there can be users with one of the two keys missing.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can do this with conditional aggregation in a having clause, assuming there are only two readings per user.

select user_id
from table t
group by user_id
having sum(case when key = 'bp_systolic' then value end) < 120 and
       sum(case when key = 'bp_diastolic' then value end) < 80;

Upvotes: 4

Related Questions