SQL query with grouping

Question

I have a problem by solving following task:

'Show for every seller how much he earned (quantity * product_price) by selling the product PS4 in the year 2013'

The relations are:

seller(id , seller_name, advertised_by);
product( id, product_name, product_price);
sale(id, seller_id, product_id, quantity, date);

I inserted following data:

INSERT into seller VALUES
(1,'Bob',NULL),
(2,'Mary',1),
(3,'Peter',1),
(4,'Parker',1),
(5,'Jeff',1);

INSERT INTO product VALUES
(1,'PS4',100),
(2,'XBOX One',300),
(3,'Laptop',500);

INSERT INTO sale VALUES
(1,1,1,1,'4 5 2013'),
(2,2,1,2,'5 6 2013'),
(3,3,1,3,'6 6 2013'),
(4,4,1,4,'6 6 2013');

I know not using foreign keys or using varchar for date isn't good but I want to have the example being simple.

SELECT seller.id,seller.seller_name, (sale.quantity * product.price) AS sale
FROM seller,product,sale 
WHERE product.id = sale.product_id
AND product.product_name = 'PS4'
AND sale.date like '%2013'
GROUP by seller.id;

I know that I have to use a GROUP BY but grouping by seller.id doesn't work.

Answer 1

You need to group by every column that isn't aggregated, and apply an aggregate function to the others. Here, you need to add sellar_name to the group by clause (which shouldn't change the grouping, as the id is already unique), and sum the sales.

Also, as a side note, using implicit joins (having more than one table in the from clause) has been deprecated for several years, and it's recommended you use an explicit join instead:

SELECT   seller.id,seller.seller_name, SUM(sale.quantity * product.price) AS sale
FROM     seller
JOIN     sale ON sale.seller_id = seller.id
JOIN     product ON product.id = sale.product_id
WHERE    product.product_name = 'PS4' AND sale.date like '%2013'
GROUP BY seller.id;