CODEWITHSUNDEEP
sql
Jeff
Jeff

Reputation: 11

SQL group by, what am I doing wrong?

The situation is as follows:

Find the top 5 Community Areas by average College Enrollment.

The DB is stored as SCHOOLS.

%sql SELECT COLLEGE_ENROLLMENT, COMMUNITY_AREA_NAME FROM SCHOOLS GROUP BY COLLEGE_ENROLLMENT;

I understand that this would give me the college enrollment by community, but I get the error message of this:

(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Exception('SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0119N An expression starting with "COMMUNITY_AREA_NAME" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803\r SQLCODE=-119')

Can anyone give me a lead on what I'm doing wrong here?

Thank you!

Upvotes: 0

Views: 868

Answers (2)

zealous
zealous

Reputation: 7503

Try the following it should work.

Find the top 5 Community Areas by average College Enrollment.

SELECT 
    COMMUNITY_AREA_NAME,
    AVG(COLLEGE_ENROLLMENT) AS AVG_ENROLL
FROM SCHOOLS 
GROUP BY 
     COMMUNITY_AREA_NAME
ORDER BY
    AVG(COLLEGE_ENROLLMENT) DESC
LIMIT 5
;

Upvotes: 0

cactus4
cactus4

Reputation: 128

When using GROUP BY anything you put after the SELECT clause has to be used in the GROUP BY clause or an aggregate function, like SUM(). In your case you would need to place COMMUNITY_AREA_NAME in the GROUP BY clause or remove it from the SELECT clause to get the error to go away. That said, I don't think this query is quite what you want - I would do something like this:

SELECT COMMUNITY_AREA_NAME, SUM(COLLEGE_ENROLLMENT) AS TOTAL_ENROLLED FROM SCHOOLS GROUP BY COMMUNITY_AREA_NAME, ORDER BY TOTAL_ENROLLED DESC;

Explanation:

  • SUM(COLLEGE_ENROLLMENT): Total up the enrollment of all schools that are in a single COMMUNITY_AREA_NAME.
  • AS TOTAL_ENROLLED: Give the result from SUM() a name so we can easily refer to it later in the ORDER BY clause.
  • ORDER BY TOTAL_ENROLLED DESC: Sort the output by TOTAL_ENROLLED and put the biggest numbers at the top.

Upvotes: 1

Related Questions