CODEWITHSUNDEEP
sqlsql-servergroup-by
Ellis Jordan
Ellis Jordan

Reputation: 47

Error: Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I am trying to complete an assignment on Microsoft SQL Management Server using the following instructions:

  1. Create a view named VIEW1 that includes the following fields: store code, store name, region code, region name.
  2. Create a view named VIEW2 that includes the following fields: employee code, employee first name, employee last name, store code, store name.
  3. Create a view named VIEW3 that includes the following fields: store code, store name, the count of employees in the store. Test this view with the command

I have completed steps 1-2, but when I get to step 3 I run into a problem. Here is my step 3:

CREATE VIEW VIEW3
AS
SELECT s.STORE_CODE, s."STORE_NAME", COUNT(*) AS EMPLOYEES_COUNT 
FROM EMPLOYEE e join STORE s ON s.STORE_CODE = e.STORE_CODE
GROUP BY e.STORE_CODE;

I keep getting the same error when I try to execute it:

Column 'STORE.STORE_CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What do I need to change?

Upvotes: 0

Views: 846

Answers (1)

Digvijay S
Digvijay S

Reputation: 2715

Each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list

You need to add STORE_NAME column in the group by list 

CREATE VIEW VIEW3
AS
SELECT s.STORE_CODE, s."STORE_NAME", COUNT(*) AS EMPLOYEES_COUNT 
FROM EMPLOYEE e
JOIN STORE s ON s.STORE_CODE = e.STORE_CODE 
GROUP BY s.STORE_CODE, s."STORE_NAME";

Upvotes: 2

Related Questions