Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column when try to create a View

Question

I try to create a view from my tables. When I create a view like this from 1 table it works nice:

CREATE VIEW v2 AS SELECT section_id, section_name, count(*) AS num, count(*) as num1 FROM section_of_science GROUP BY section_id; select * from v2;

But when I try to create a view from two tables it doesn't work:

CREATE VIEW v3 AS SELECT section_of_science.section_id, section_of_science. section_name, scientific_areas.areas_name, count(*) AS num, count(*) as num1 FROM section_of_science, scientific_areas GROUP BY section_id; select * from v3;

And I get this error:

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Lab3.scientific_areas.areas_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

what am I doing wrong?

Let me know if you need more information.

Tip of today: Always use modern, explicit JOIN syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! — jarlh
– jarlh, Commented May 18, 2020 at 10:59
Tip 2: You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh
– jarlh, Commented May 18, 2020 at 11:00

Gordon Linoff · Accepted Answer · 2020-05-18 11:00:56Z

Your first query works because you are aggregating by the primary key. You are clearly learning SQL, so you should include all unaggregated columns in the GROUP BY. Later you can learn about small efficiencies.

Your second should be written as:

SELECT ss.section_id, ss.section_name, ss.areas_name, count(*) AS num FROM section_of_science ss join scientific_areas as ON -- JOIN CONDITIONS HERE GROUP BY ss.section_id, ss.section_name, ss.areas_name;

Notes:

Use meaningful table aliases (abbreviations for the table names).
NEVER use commas in the FROM clause.
ALWAYS use proper, explicit, standard, readable JOIN syntax.
Your query is missing ON conditions for the JOIN.
List all the unaggregated columns in the GROUP BY.

Collectives™ on Stack Overflow

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column when try to create a View

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related