HAVING vs WHERE in SQL

[mrchow19910319]

```
CREATE table table_name
(
  name TEXT,
  subject TEXT,
  number_grade INTEGER
  );

INSERT INTO table_name (name, subject, number_grade) VALUES ("person_a","math",81);
INSERT INTO table_name (name, subject, number_grade) VALUES ("person_a","science",75);
INSERT INTO table_name (name, subject, number_grade) VALUES ("person_b","math",76);
INSERT INTO table_name (name, subject, number_grade) VALUES ("person_b","science",90);
INSERT INTO table_name (name, subject, number_grade) VALUES ("person_c","math",81);
INSERT INTO table_name (name, subject, number_grade) VALUES ("person_c","science",100);
INSERT INTO table_name (name, subject, number_grade) VALUES ("person_c","chemistry",90);

SELECT * FROM table_name;
```

Name of the game is to find everyone who had a grade that is bigger than 80 for every subject they took. 

I tried this: 

```
SELECT name FROM table_name 
WHERE number_grade > 80
GROUP BY NAME;
```

and it gives me all the names in the table. 
and the correct answer is this: 

```
SELECT name FROM table_name 
GROUP BY name
HAVING min(number_grade) > 80; 
```

so the difference between HAVING and WHERE is that WHERE can only apply to one element inside the database? and HAVING coming after GROUP BY while WHERE is always before GROUP BY? 

am I understanding this correctly? 

[Limecat86]

1 hour ago, mrchow19910319 said:

--SNIP--

so the difference between HAVING and WHERE is that WHERE can only apply to one element inside the database? and HAVING coming after GROUP BY while WHERE is always before GROUP BY? 

am I understanding this correctly? 

Yes that's correct. HAVING operates on aggregate functions (ex. COUNT) on grouped results. WHERE operates on each individual row.

Found some very good explanations on stackoverflow: https://stackoverflow.com/questions/9253244/sql-having-vs-where 

[mrchow19910319]

22 minutes ago, Limecat86 said:

Yes that's correct. HAVING operates on aggregate functions (ex. COUNT) on grouped results. WHERE operates on each individual row.

Found some very good explanations on stackoverflow: https://stackoverflow.com/questions/9253244/sql-having-vs-where 

Thanks! 

[reniat]

No offense, but this is a pretty google-able question. I don't want to discourage question asking, but you've asked quite a few smaller questions here and I think you might be doing yourself a disservice by not growing your google-fu skills. Being able to seek out information is an important skill, and it takes practice. By going straight to LTT forums to have people give you the answer, you are not letting yourself grow. again, asking questions is never bad, but it's also good to expose yourself to scouring the web for solutions.

[mrchow19910319]

48 minutes ago, reniat said:

No offense, but this is a pretty google-able question. I don't want to discourage question asking, but you've asked quite a few smaller questions here and I think you might be doing yourself a disservice by not growing your google-fu skills. Being able to seek out information is an important skill, and it takes practice. By going straight to LTT forums to have people give you the answer, you are not letting yourself grow. again, asking questions is never bad, but it's also good to expose yourself to scouring the web for solutions.

totally agree with what you said. that being said, I have found the solution on google before someone replied me here... 

I think in the future I will google more, then come here ask questions. 

about all the other questions, I really don't know any thing about it. all the questions I posted about web server etc.

It is pretty new to me and I had been googling those things for a while now. digital ocean has some similar question, stack overflow has some similar question, but i have yet found a solution that works for me.

 

so I will try to google more and find out more myself, if I did not find any solution, I will include my working progress and quote other people's solution online and tell you guys which one I have tried, yet failed, which one I did not try cos I don't know how to. 

 

 

nonetheless thanks for the suggestion!