oracle - SQL query that returns a subset of table plus column containing count for whole table -

oracle - SQL query that returns a subset of table plus column containing count for whole table -

i have 1 table named people (sex, age, weight) listed here:

sex age weight m 10 81 f 21 146 m 32 179 f 40 129 f 58 133

i have next info returned (sex, age, weight, count(*) sexcount people age < 35):

sex age weight sexcount m 10 81 2 f 21 146 3 m 32 179 2

i have found answers work if want homecoming people in table (count without group).

but have not found reply if want sexcount include total count whole table...and not total count returned subset. in other words, want returned info include people less 35 years old, want sexcount include count people in table regardless of age.

anyone know query homecoming info want table illustration above? using oracle if makes difference.

i tried using sql phrase in query:

count(*) over(partition sex) sexcount

but counted number in query results, , not in whole table require (and explained above). thanks.

you're looking single column on output built using different criteria rest of group. no matter sql system, need invoke sec record-set.

thankfully, since you're not looking aggregate query, can done single subquery on list.

select p.sex, p.age, p.weight, t.sexcount people p inner bring together (select sex, count(*) sexcount people grouping sex) t on p.sex = t.sex p.age < 35;

sql oracle