0

I have one 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 would like to have the following data returned (Sex, Age, Weight, Count(*) as SexCount from PEOPLE where Age < 35):

Sex Age Weight  SexCount
M   10  81      2
F   21  146     3
M   32  179     2

I have found answers that work if I want to return all the people in the table (count without group).

But I have not found an answer if I want SexCount to include the total count from the whole table...and not just the total count from my returned subset. In other words, I want my returned data to just include people who are less than 35 years old, but I want the SexCount to include the count for all people in the table regardless of age.

Anyone know a query that will return the data I want from the table example above? I am using Oracle if it makes a difference.

I tried using this SQL phrase in my query: 

COUNT(*) OVER(PARTITION BY Sex) as SexCount

But it only counted the number that were in my query results, and not in the whole table as I require (and explained above). Thanks.

Community
  • 1
  • 1
Joe Gayetty
  • 1,521
  • 2
  • 22
  • 35
  • Most likely impossible, but you should provide the queries you already have to let people take a look. – Justin Kiang Oct 21 '14 at 16:29
  • SQL Server has a function called partition by which will allow to aggregate any way you please...Not sure of the oracle equivalent. It would help if you post some sample data and expected results – CSharper Oct 21 '14 at 16:29

1 Answers1

3

You're looking for a single column on your output that is built using different criteria than the rest of the group. No matter your SQL system, you will need to invoke a second record-set.

Thankfully, since you're not really looking for an aggregate query, this can be done with a single subquery on your FROM list.

SELECT P.Sex, P.Age, P.Weight, T.SexCount
FROM PEOPLE as P
INNER JOIN (SELECT Sex, COUNT(*) As SexCount FROM PEOPLE GROUP BY Sex) AS T
  ON P.Sex = T.Sex
WHERE P.Age < 35;
DougM
  • 2,808
  • 17
  • 14
  • Thanks for the reply. I will give it a try and reply back with my findings. – Joe Gayetty Oct 21 '14 at 16:36
  • My query was a bit more complex than my example of course, but Oracle kept giving me an 'invalid identifier' error with your suggested answer. I ended up putting a Select statement in my Select statement like this "SELECT (SELECT COUNT(*) FROM Lei_Job_Time LJT where...) as Count, Column2, Column3 FROM...WHERE..." and it worked! Your answer may be just fine but I could not get it to work. – Joe Gayetty Oct 21 '14 at 18:10
  • You may want to try setting up an example on SQLFiddle or a similar site in the future; just be sure to switch to a compatible rdms for what you're targeting. – DougM Oct 21 '14 at 18:25