Group By Expression Error

Question

Have an issue regarding the queries with GROUP BY. This is the following statement that I use so I can create the query:

SQL> SELECT invoice_num, technician_num, service_advisor, invoice_date_in, customer_id
2 FROM invoices_TP
3 GROUP BY invoice_date_in;

And this is error I get every time I try to implement it:

SELECT invoice_num, technician_num, service_advisor, invoice_date_in, customer_id
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

I checked the table and all the SELECT statement are according to the columns the table contain.

Here are the rest of the statements with tables and information already created:

CREATE TABLE parts_TP
(part_num NUMBER (8),
 part_unit_price NUMBER (6,2)
     CONSTRAINT parts_TP_prt_unit_price_nn NOT NULL, 
 part_description VARCHAR2 (40)
     CONSTRAINT parts_TP_prt_desc_nn NOT NULL,
 CONSTRAINT parts_TP_prt_num_pk PRIMARY KEY (part_num));

CREATE TABLE vehicles_TP
(car_vin VARCHAR2 (17),
 car_year CHAR (8)
     CONSTRAINT veh_TP_car_year_nn NOT NULL,
 car_manufacturer VARCHAR2 (32)
     CONSTRAINT veh_TP_car_man_nn NOT NULL,
  car_color VARCHAR2 (15)
     CONSTRAINT veh_TP_car_col_nn NOT NULL,
 car_engine_type VARCHAR2 (32)
     CONSTRAINT veh_TP_car_eng_nn NOT NULL, 
 CONSTRAINT veh_TP_car_vin_pk PRIMARY KEY (car_vin));

CREATE TABLE customers_TP
(customer_id NUMBER (6),
 customer_last_name VARCHAR2 (15)
     CONSTRAINT cust_TP_cust_lname_nn NOT NULL,
 customer_first_name VARCHAR2 (10)
     CONSTRAINT cust_TP_cust_fname_nn NOT NULL,
 customer_street VARCHAR2 (18)
     CONSTRAINT cust_TP_cust_street_nn NOT NULL,
 customer_city VARCHAR2 (15)
     CONSTRAINT cust_TP_cust_city_nn NOT NULL,
 customer_state CHAR (2)
     CONSTRAINT cust_TP_cust_state_nn NOT NULL,
 customer_zip_code CHAR (5)
     CONSTRAINT cust_TP_cust_zip_nn NOT NULL,
 customer_phone_num CHAR (12)
     CONSTRAINT cust_TP_cust_phone_nn NOT NULL,
 customer_alt_phone CHAR (12),
 customer_license_num varchar2 (8)
     CONSTRAINT cust_TP_cust_licnum_nn NOT NULL,
 CONSTRAINT cust_TP_cust_id_pk PRIMARY KEY (customer_id));

CREATE TABLE technicians_TP
(technician_num NUMBER (2),
 technician_last_name VARCHAR2 (10)
     CONSTRAINT tech_TP_tech_lname_nn NOT NULL,
 technician_first_name VARCHAR2 (8)
     CONSTRAINT tech_TP_tech_fname_nn NOT NULL,
 CONSTRAINT tech_TP_tech_num_pk PRIMARY KEY (technician_num));

CREATE TABLE ownerships_TP
(customer_id NUMBER (6),
 car_vin VARCHAR2 (17)
     CONSTRAINT owner_TP_car_vin_not_null NOT NULL,
 CONSTRAINT owner_TP_cust_id_prime_key PRIMARY KEY (customer_id, car_vin),
 CONSTRAINT owner_TP_cust_num_fk FOREIGN KEY (customer_id)
     REFERENCES customers_TP (customer_id),
  CONSTRAINT ownerships_TP_car_vin_fk FOREIGN KEY (car_vin)
     REFERENCES vehicles_TP (car_vin));

CREATE TABLE invoices_TP
(invoice_num NUMBER (6),
 customer_id NUMBER (6)
     CONSTRAINT inv_TP_cust_nn NOT NULL, 
car_vin VARCHAR2 (17)
     CONSTRAINT inv_TP_car_vin_nn NOT NULL,     
 invoice_date_in DATE
     CONSTRAINT inv_TP_inv_date_in_nn NOT NULL,
 invoice_date_out DATE 
     CONSTRAINT inv_TP_inv_date_out_nn NOT NULL,
 invoice_sales_tax NUMBER (5,2)
     CONSTRAINT inv_TP_inv_sls_tax_nn NOT NULL,
 invoice_mileage_in Number (6)
     CONSTRAINT inv_TP_inv_mile_nn NOT NULL,  
 technician_num NUMBER (2)
     CONSTRAINT inv_TP_tech_num_nn NOT NULL,
 service_advisor VARCHAR2 (15)
     CONSTRAINT inv_TP_serv_nn NOT NULL,
 CONSTRAINT inv_TP_inv_num_pk PRIMARY KEY (invoice_num),   
 CONSTRAINT inv_TP_car_vin_fk FOREIGN KEY (car_vin)
     REFERENCES vehicles_TP (car_vin),
 CONSTRAINT inv_TP_inv_car_vin_fk FOREIGN KEY (customer_id)
     REFERENCES customers_TP (customer_id),
 CONSTRAINT inv_TP_tech_num_fk FOREIGN KEY (technician_num)
     REFERENCES technicians_TP (technician_num));

CREATE TABLE parts_sold_TP
(invoice_num NUMBER (6),
 part_num NUMBER (8)
      CONSTRAINT prt_num_TP_not_null NOT NULL,
 part_unit_price NUMBER (6,2)
     CONSTRAINT prt_unit_price_not_null NOT NULL,
 part_qty_sold CHAR (3)
     CONSTRAINT prt_qty_sold_TP_not_null NOT NULL,
 CONSTRAINT prt_sls_TP_inv_num_prime_key PRIMARY KEY (invoice_num, part_num),  
 CONSTRAINT prt_sls_TP_inv_num_fk FOREIGN KEY (invoice_num)
     REFERENCES invoices_TP (invoice_num),
 CONSTRAINT prt_sls_TP_part_num_fk FOREIGN KEY (part_num)
     REFERENCES parts_TP (part_num));

/* rem this sequence is used to increase with every new customer */
CREATE SEQUENCE cust_id_seq
 INCREMENT BY 1
 START WITH 367909
 NOCYCLE
 NOCACHE;
/* this sequence is used to increase with every new invoice */
CREATE SEQUENCE invoice_num_seq
 INCREMENT BY 1
 START WITH 160000
 NOCYCLE
 NOCACHE;

Any help would be grateful.

Thanks!

Answer 1

when you use group by expressions, your selected columns could be either columns from group by, or agregate functions (like min, max, count, etc..).

data example:

NAME    LAST_NAME    AMOUNT   
----    ---------    ------
Bob     Marley       100
Freddy  Mercury      120
Bob     Marley       150

you want to output something like:

NAME    LAST_NAME    AMOUNT   
----    ---------    ------
Bob     Marley       250
Freddy  Mercury      120

if you do

select name, last_name, sum(amount)
from my_table
group by name

I will get your error because it is not clear what to do with last_name for Bob. There are 2 records and we need to display only 1, how should DB choose?

Here is 2 posible solutions.

1. use last_name in group by:

   select name, last_name, sum(amount)
   from my_table
   group by name, last_name
   

2. use agregate funciton on last_name:

   select name, max(last_name), sum(amount)
   from my_table
   group by name