[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

GROUPING

I am trying to group by EEO_1classification.
I put  SELECT EEO_1CLASSIFICATION,LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME=EEO_1CLASSIFICATION
GROUP BY EEO_1CLASSFICATION;

But I got the error message
Server: Msg 8120, Level 16, State 1, Line 1
Column 'EMPLOYEES.LAST_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

WHAT AM I DOING WRONG? I DID TRY EVERYTHING I CAN DO BUT I CAN'T FIGURE IT OUT.
HELP..

HERE IS MY CODE.




CREATE TABLE EMPLOYEES

(EMPLOYEES_NUM CHAR(2) PRIMARY KEY,

LAST_NAME CHAR(15),

FIRST_NAME CHAR(15),

ADDRESS CHAR(50),

CITY CHAR(15),

STATE CHAR(2),

TELEPHONE_AREA_CODE CHAR(3),

TELEPHONE_NUMBER CHAR(7),

EEO_1CLASSIFICATION CHAR(50),

HIRE_DATE CHAR(8),

SALARY DECIMAL(8,2),

GENDER CHAR(1),
RACE CHAR(30),

AGE CHAR(2));

The command(s)completed successfully.

Data insertion



INSERT INTO EMPLOYEES

VALUES

('01','EDELMAN','GLENN','175 BISHOPS LANE','LA
JOLLA','CA','619','5550199',
'SALES WORKERS','20061007','21500.00','F','CAUCASIAN','64');

INSERT INTO EMPLOYEES

VALUES
(
'02','MCMULLEN','ERIC','763 CHURCH ST','LEMON
GROVE','CA','619','5550133',
'SALES WORKERS','20021101','13500.00','M','CAUCASIAN','20');

INSERT INTO EMPLOYEES

VALUES
(
'03','SLENTZ','RAY','123 TORREY DR','NORTH CLAIRMONT','CA','619',
'5550123','OPERATIVES','19980715','23500.00','F','HISPANIC','28');

INSERT INTO EMPLOYEES

VALUES
(
'04','BROUN','ERIN','2045 PARKWAY APT213','ENCINITAS',
'CA','760','5550100','SALES
WORKERS','20030312','10530.00','F','CAUCASIAN','24');

INSERT INTO EMPLOYEES
VALUES
(
'05','CARPENTER','DONALD','927 SECOND
ST','ENCINITAS','CA','619','5550154',
'OFFICE/CLERICAL','20030218','15000.00','M','AFRICAN-AMERICAN','18');

INSERT INTO EMPLOYEES

VALUES
(
'06','ESQUIVEZ','DAVID','10983 N.COAST HWY
APT902','ENCINITAS','CA','760','5550108',
'OPERATIVES(SEMI-SKILLED)','20030725','18500','M','HISPANIC','25');
INSERT INTO EMPLOYEES
VALUES
(
'07','SHARP','NANCY','10793 MONTCANO RD','RAMONA','CA','858','5550135',
'SALES WORKERS','20030712','21000.00','F','CAUCASIAN','24');

INSERT INTO EMPLOYEES
VALUES
(
'08',
'MCNAMARA',
'JUANITA',
'923 PARKWAY',
'POWAY',
'CA',
'619',
'5550206',
'OFFICE/CLERIACAL',
'19991029',
'25500.00',
'F',
'HISPANIC',
'32');

INSERT INTO EMPLOYEES
VALUES
(
'09',
'NGUYEN',
'MEREDITY',
'10583 ARENAS ST',
'LA JOLLA',
'CA',
'619',
'5550102',
'TECHNIICAN',
'19980927',
'43000.00',
'M',
'CAUCASIAN',
'25');

INSERT INTO EMPLOYEES
VALUES
(
'10',
'STEPHENS',
'HARVERY',
'7863 HIGH BLUFF DRIVE',
'LA JOLLA',
'CA',
'619',
'5550123',
'OFFICE&MANAGERS',
'19980301',
'75000.00',
'M',
'CAUCASIAN',
'51');

INSERT INTO EMPLOYEES
VALUES
(
'11',
'VU',
'MATTHEW',
'981 JOVEY PINES ROAD',
'LA JOLLA',
'CA',
'619',
'5550138',
'TECHNICIAN',
'20000816',
'37000.00',
'M',
'ASIAN',
'26');

INSERT INTO EMPLOYEES
VALUES
(
'12',
'AVERY',
'LEDONNA',
'198 HAVERNA DR',
'DELMAR',
'CA',
'619',
'5550135',
'CRAFT WORKERS(SKILLED)',
'20030328',
'21000.00',
'F',
'AFRICAN-AMERICAN',
'23');

INSERT INTO EMPLOYEES
VALUES
(
'13',
'DROHOS',
'CRAIG',
'X',
'SOLANO BEACH',
'CA',
'619',
'5550202',
'OFFICALS & MANAGERS',
'20000615',
'51000.00',
'M',
'CAUCASIAN',
'28');


INSERT INTO EMPLOYEES
VALUES
(
'14',
'MEIER',
'ELAINE',
'9703 DRAVIE LANE',
'DEMAR',
'CA',
'858',
'5550112',
'SALES WORKERS',
'20000910',
'20500.00',
'F',
'ASIAN',
'51');

INSERT INTO EMPLOYEES
VALUES
(
'15',
'QUILLIAN',
'STANLEY',
'98542 WANDERING ROAD APT2-B',
'DELMAR',
'CA',
'760',
'5550198',
'OPERATIVES(SEMI-SKILLED)',
'19991216',
'23000.00',
'M',
'AMERIAN-INDIAN',
'29');

INSERT INTO EMPLOYEES
VALUES
(
'16',
'TYINK',
'THOMAS',
'87592 VACIFIC HEIGHTS BLVD',
'DELMAR',
'CA',
'858',
'5550159',

'CRAFT WORKERS(SKILLED)',
'20010501',
'19000.00',
'M',
'AFRICAN-AMERICAN',
'32');

INSERT INTO EMPLOYEES
VALUES
(
'17',
'VANCE',
'BRENT',
'927 CYUTHIA LANE',
'POWAY',
'CA',
'858',
'5550147',
'SALES WORKERS',
'20010329',
'10530.00',
'M',
'CAUCASIAN',
'22');


CREATE TABLE JOB_TITLE
(
EEO_1_CLASSIFICATION CHAR(50),
JOB_TITLE CHAR(100),
JOB_DESCRIPTION CHAR(300),
EXEMPT_NON_EXEMPT_ STAUTS CHAR(20));

INSERT INTO JOB_TITLE

VALUES
(
'OFFICE/CLERICAL',
'ACCOUNTING CLERK',
'COMPUTES,CLASSIFIES,RECORDS,AND VERIFIES NUMBERICAL DATA
FOR USE IN MAINTAINING ACCOUNTING RECORDS',
'NON_EXEMPT');

INSERT INTO JOB_TITLE

VALUES
(
'TECHNICIAN',
'COMPUTER SUPPORT SPECIALIST',
'INSTALL,MODIFIES,AND MAKES MINOR REPAIRS TO PERSONAL COMPUTER HARDWARE AND SOFTWARE
SYSTEMS AND PROVIDES TECHNICAL ASSISTANCE AND TRAINING TO SYSTEM USERS',
'NON_EXEMPT');

INSERT INTO JOB_TITLE

VALUES
(
'OFFICE/CLERICAL',
'DIRECTOR OF FINANCE&ACCOUNTING',
'PLANS AND DIRECTS THE FINANCE AND ACCOUNTING ACTIVITIES FOR KUDLER FINE FOODS',
'EXEMPT');

INSERT INTO JOB_TITLE

VALUES
(
'OFFICALS&MANAGERS',
'ASSISTANT STORE MANAGER',
'ACTIVITES OF WORKING IN DEPARTMENT OF FOOD STORE.
ASSISTS STORE MANAGER IN DAILY OPERATION OF STORE',
'EXEMPT');

INSERT INTO JOB_TITLE

VALUES
(
'SALES WORKERS',
'BAGGER',
'PLACE CUSTOMER ORDERS IN BAGS.
PERFORMS CARRYOUT DUTIES FOR COUSTOMERS',
'NON-EXEMPT');

INSERT INTO JOB_TITLE

VALUES
(
'SALES WORKERS',
'CASHIER',
'OPERATES CASH REGISTER TO ITEMIZE AND TOTAL CUSTOMER PURCHASES IN GROCERY STORE',
'NON-EXEMPT');

INSERT INTO JOB_TITLE

VALUES
(
'OFFICE/CLERICAL',
'STOCKER',
'STORES,PRICES AND RESTOCKS MERHANDISE DISPLAYS IN STORE',
'NON_EXEMPT');

INSERT INTO JOB_TITLE

VALUES
(
'CRAFT WORKERS(SKILLED)',
'RETAIL ASSISTANT.BAKERY&PASTRY',
'OBTAINS OR PREPARES FOOD ITEMS REQUESTED BY CUSTOMERS IN RETAIL FOOD STORE',
'NON-EXEMPT');

INSERT INTO JOB_TITLE

VALUES
(
'OPERATIVES(SEMI-SKILLED)',
'ASSISTANT.BUTCHERS&SEAFOOD SPECIALISTS',
'OBTAINS OR PREPARES FOOD ITEMS REQUESTED BY CUSTOMERS IN RETAIL FOOD STORE',
'NON-EXEMPT');












0
drmopco
Asked:
drmopco
  • 2
  • 2
  • 2
2 Solutions
 
sajuksCommented:
try
SELECT EEO_1CLASSIFICATION,LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME=EEO_1CLASSIFICATION
GROUP BY EEO_1CLASSFICATION,LAST_NAME;

0
 
orionpmCommented:
The group by function is expecting all fields in the select that are not aggregates to be included in the group by clause.  

Try this...

SELECT EEO_1CLASSIFICATION,LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME=EEO_1CLASSIFICATION
AND EEDO_1CLASSIFICATION IN (SELECT EEDO_1CLASSIFICATION FROM EMPLOYEES
GROUP BY EEDO_1CLASSIFICATION)
0
 
drmopcoAuthor Commented:
Thank you for your time btw the output supposed to be nothing shows up?? except 2 tables??
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
sajuksCommented:
from the data what u pasted i think you've mixed up the join condition and the query that ur actually looking for should be
SELECT EMPLOYEES.EEO_1CLASSIFICATION , EMPLOYEES.LAST_NAME
FROM EMPLOYEES,JOB_TITLE
WHERE EMPLOYEES.EEO_1CLASSIFICATION =JOB_TITLE.EEO_1_CLASSIFICATION
GROUP BY EMPLOYEES.EEO_1CLASSIFICATION,LAST_NAME;
0
 
drmopcoAuthor Commented:
Outstanding.
0
 
orionpmCommented:
Also since you are grouping by both EEO_1CLASSIFICATION , EMPLOYEES.LAST_NAME and not just EEO_1CLASSIFICATION  you could remove the group by clause and use a select distinct...

SELECT DISTINCT EMPLOYEES.EEO_1CLASSIFICATION , EMPLOYEES.LAST_NAME
FROM EMPLOYEES,JOB_TITLE
WHERE EMPLOYEES.EEO_1CLASSIFICATION =JOB_TITLE.EEO_1_CLASSIFICATION
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now