MySQL: Aggregate function: group by joined variable

Question

I thought I had this down, but it looks like I'm doing something wrong, and I can't be certain what.

UPDATE: I believe the error has something to do with a connection between the customer table and the order_details table. Not sure what exactly it is yet, but any insight would be awesome!

I do not receive any errors, it just displays nothing. Is it the syntax? Do I have something weird going on? Or is it perhaps the organization of my tables?

Statement Having Trouble With

SELECT customers.ship_state as 'State',
        SUM(ROUND(products.product_cost * order_details.quantity_purchased, 2)) as 'Total Sales'
FROM customers
JOIN order_details on order_details.customer_id = customers.customer_id
JOIN products on products.product_id = order_details.product_id
GROUP BY customers.ship_state
ORDER BY customers.ship_state ASC;

THESE ARE THE CREATE TABLE STATEMENTS:

CREATE TABLE customers 
(
customer_ID             int(4)              UNIQUE,
credit_card_ID          int(10)             NOT NULL,
cus_fname               varchar(35)         NOT NULL,
cus_lname               varchar(35)         NOT NULL,
company_name            varchar(35)         NOT NULL,
company_phone           varchar(15)         NOT NULL,
ship_street             varchar(50)         NOT NULL,
ship_city               varchar(25)         NOT NULL,
ship_state              varchar(2),
ship_zip                int(10)             NOT NULL,
PRIMARY KEY (customer_id),
FOREIGN KEY (credit_card_ID) REFERENCES credit_cards (credit_card_ID)
);

CREATE TABLE products 
(
product_ID              int(10)             UNIQUE,
product_name            varchar(30)         NOT NULL,           
quantity_stocked        int(5),
product_cost            dec(8,2)            NOT NULL,
PRIMARY KEY (product_ID)
);

CREATE TABLE order_details 
(
order_ID                int(10)             UNIQUE,
customer_ID             int(10)             NOT NULL,
product_ID              int(10)             NOT NULL,
quantity_purchased      int(5),
PRIMARY KEY (order_ID),
FOREIGN KEY (order_ID)      REFERENCES orders (order_ID),
FOREIGN KEY (customer_ID)   REFERENCES customers (customer_ID),
FOREIGN KEY (product_ID)    REFERENCES products (product_ID)
);

THESE ARE THE INSERT STATEMENTS:

INSERT INTO customers VALUES 
(100,1,'Sarah','Jones','Sports Authority','6197254829','1324 River Road','Cincinnati','OH',45225),
(101,2,'Desmond','Lowell','Sport Chalet','7023468547','3467 W Shaw Ave #103','Fresno','CA',93711),
(102,3,'Mark','Jefferson','24 HR Fitness','8005557000','3433 E Widget Ave','Palo Alto','CA',92711),
(103,4,'Jaime','Evans','Crash Athlete','2125554800','828 S Broadway','Tarrytown','NY',10591);

INSERT INTO products VALUES
(452,'Nike SS Pro Top',418, 22.99),
(861,'Nike LunarGlide 7',1082, 74.99),
(453,'Nike LS Pro Top',654, 64.99),
(454,'Nike SS Pro Top',720, 64.99),
(451,'Nike Pro Tank',480, 45.99),
(485,'Nike 5k Run Short',1402, 24.99),
(582,'Nike Fit Capri',750, 29.99),
(591,'Nike Be Fast Tight',1948, 39.99),
(847,'Nike Legend Tee',120, 34.99),
(277,'Nike Miller v-neck',1920, 44.99),
(135,'Adidas Soccer Pant',630, 34.99),
(456,'Adidas SS Breathe Top',754, 19.99),
(457,'Adidas Pro Cleat',626, 44.99),
(214,'Adidas Slide',902, 19.99),
(054,'Underarmor SL Top',854,54.99),
(059,'Undersarmor LS High Neck',1021,54.99),
(574,'Underarmor TR Short',746,24.99),
(421,'Underarmor TR Balance Shoe',858,84.99);

INSERT INTO order_details VALUE
(213,1,452,218),
(214,2,452,200),
(215,3,861,300),
(216,4,861,260),
(217,1,456,600),
(218,2,054,430),
(219,3,421,430),
(220,4,421,428),
(221,1,214,500),
(222,3,582,450),
(223,2,451,480), 
(224,4,861,300),
(225,3,591,800),
(226,2,277,400),
(227,1,591,800),
(228,4,054,300),
(229,2,059,500),
(230,3,059,521), 
(231,1,574,200),
(232,2,574,200),
(233,4,574,200),
(234,1,135,420),
(235,2,277,400),
(236,2,277,400);

your order_detail customerID's don't match the customer id's you created. Customers you have are 100-103 order details you have 1-4 as the customer ID. — xQbert, Apr 12 '16 at 21:11
Thank you....that was silly. I forgot I changed the values in one and failed to do so in the other. — Sierra, Apr 12 '16 at 21:12
I'll HAPPILY answer any questions you ask if you keep providing this level of detail. Your question was well worded and provided all the necessary details to provide a valid solution! Sometimes you just need a 2nd pair of eyes (or more) — xQbert, Apr 12 '16 at 21:17

odannyc · Accepted Answer · 2016-04-12T21:14:27.947

I copied your tables and look at the customers table! The customer ID's are in the 100's . In your order details you have your customer ID's in the 1's.

The query is right. You just need to make sure that the customer ID in the customers table matches the customer ID in the order_details table.

I guess you can do this as a workaround:

SELECT customers.ship_state as 'State',
    SUM(ROUND(products.product_cost * order_details.quantity_purchased, 2)) as 'Total Sales'
FROM customers
JOIN order_details on order_details.customer_id = customers.credit_card_id
JOIN products on products.product_id = order_details.product_id
GROUP BY customers.ship_state
ORDER BY customers.ship_state ASC;

This is awfully silly and kind of embarrassing. Thank you for helping with the error. I'll have to be more careful with this in the future... — Sierra, Apr 12 '16 at 21:13

MySQL: Aggregate function: group by joined variable

1 Answers1