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

0
votes

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?

1
sqlsql-servergroup-by
Seems fairly self explanatory... in SQL Server you can't select a column unless you group by it or aggregate it. – Dale K
You have s.STORE_CODE in the SELECT but e.STORE_CODE in the GROUP BY. That is not the same column, it is from different table – Squirrel

1 Answers

2
votes

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";