2

I have 3 tables.

BRANDS

CREATE TABLE BRANDS (
    id_brand BIGINT(4) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(25) NOT NULL,
    UNIQUE (name)
)

PRODUCTS

CREATE TABLE PRODUCTS (
    id_product BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(6) NOT NULL UNIQUE,
    name VARCHAR(30) NOT NULL,
    price FLOAT(6,2) UNSIGNED NOT NULL,
    is_discounted TINYINT(1) NOT NULL,
    discount_percentage INT(2) NOT NULL,
    discounted_price FLOAT(6,2) NOT NULL,
    description TEXT NOT NULL,
    stock INT(4) UNSIGNED NOT NULL,
    photo_1 VARCHAR(50),
    product_type_id BIGINT(4) UNSIGNED NOT NULL,
    brand_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_type_id) REFERENCES product_types (id_product_type),
    FOREIGN KEY (brand_id) REFERENCES brands (id_brand),
    is_visible TINYINT(1) NOT NULL
)

PRODUCT SALES

CREATE TABLE PRODUCT_SALES (
    id_product_sale BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    QUANTITY INT(4) UNSIGNED NOT NULL,
    sale_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_price FLOAT(12,2) UNSIGNED NOT NULL,

    user_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (user_id) REFERENCES USERS(id_user),

    product_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_id) REFERENCES PRODUCTS(id_product),

    payment_method_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (payment_method_id) REFERENCES PAYMENT_METHODS(id_payment_method),

    sale_status_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (sale_status_id) REFERENCES SALE_STATUS(id_sale_status)

)

I need a query to display the ammount of produces sold per brand. I should get the following

  • Intels - 27
  • Western - 5
  • GeForce - 10

But i get

  • Intels - 42

This is how i tried to get the result i need.

SELECT b.name, sum(ps.quantity)
FROM Product_Sales ps 
JOIN Products p
ON ps.product_id = p.id_product
JOIN Brands b 
ON b.id_brand = p.brand_id

What's wrong with my query? Thanks in advance.

  • 1
    What version of MySQL are you running? On most servers, that query would have failed. Check your SQL_MODE and add sql_mode = 'ONLY_FULL_GROUP_BY' to it - without it, your queries will return a random answer - i.e. in this case, the server chose to show Intel, but there's nothing to stop it showing Western tomorrow. – Vérace May 23 '18 at 2:13
2

Basically, you're missing a GROUP BY! To answer your question, I did the following:

Created tables (relevant fields only):

CREATE TABLE brand
(
  brand_id INTEGER,
  name VARCHAR(25)
);

CREATE TABLE product 
(
  product_id INTEGER,
  brand_id INTEGER,
  name VARCHAR(30) NOT NULL
);

CREATE TABLE product_sale 
(
  product_sale_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);

Then I created some sample records:

INSERT INTO brand VALUES (1, 'Ferrari'), (2, 'Lamborghini'), (3, 'Porsche');

INSERT INTO product VALUES (1001, 1, 'TestaRossa'), (1002, 1, 'Dino'), (2001, 2, 'Countach'), (2002, 2, 'Diabolo'), (3001, 3, 'Cayenne'), (3002, 3, '911'), (3003, 3, 'Boxter');

INSERT INTO product_sale VALUES (500001, 2001, 45), (500002, 1001, 12), (500003, 3003, 7), (500004, 3001, 750), (500005, 3001, 400);

and then ran this query:

SELECT 
  b.name AS "Brand name", 
--  p.name as "Product name", 
  SUM(ps.quantity) AS "Quantity"
FROM brand b
JOIN product p
ON b.brand_id = p.brand_id
JOIN product_sale ps
ON p.product_id = ps.product_id
GROUP BY b.name;  -- p.name

I left in the extra p.name fields (commented out with --) so that you can see how the query was constructed. Take out the comments and have a look.

Result:

Brand name  Quantity
Ferrari     12
Lamborghini 45
Porsche     1157

All of this can be found at db-fiddle here. For future reference, it's better to have a Minimum Complete and Verifiable Example (preferably complemented with a fiddle) which a) means that everyone is singing off the same hymnsheet - i.e. your data and b) there's no duplication of effort. You will obtain quicker and better answers with a well-constructed fiddle. You might also like to take a look here, and maybe take the tour and visit the helpdesk.

These sites are a great resource, all the more so if you take the time and effort to follow the guidelines. p.s. welcome to the forum!

  • Had a product_sale record with quantity and product_id inverted! :( - it's all good now! – Vérace May 23 '18 at 2:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.