php - How do i aggregate data from a table to show the sum of products based on the category using each month as key

Question

create table products
    (
        id int not null,
        productname varchar(45),
        productcategory varchar(45),
        productprice decimal(8,2),
        datepurchased varchar(45),
        primary key (id)
    );

 INSERT INTO products (id, productname, productcategory, productprice, datepurchased) VALUES
 (1, 'Toy car', 'toys', 200.30, '2017 December'),
  (2, 'Phone', 'phone', 50.00, '2017 December'),
  (3, 'Disk drive', 'Accessories', 10.00, '2018 January'),
   (4, 'Mouse', 'Accessories', 20.30, '2018 January'),
   (5, 'Baby doll', 'toys', 100.00, '2018 February'),
    (6, 'Toy car', 'toys', 40.10, '2018 March');

My sql fiddle: http://sqlfiddle.com/#!9/4ff0cf/1/0 The results should look like this.

Month           |  ProductCategory    | Amount
-------------------------------------------------
2017 December   |  toys               | 200.30
2018 January    |  phone              |  50.00
2018 January    |  Accessories        |  30.30
2018 February   |  toys               |  100.00
2018 March      |  toys               |   40.10

If you notice, Accessories has multiple entries for January and it is summed.

Please include a sql fiddle in your answer if you can. Thanks.

标签： phpmysqlsqlrdbms

Answer 1

Fiddle here: http://sqlfiddle.com/#!9/4ff0cf/15/0

What you want is to do a group by with multiple params:

SELECT id, productcategory, sum(productprice), datepurchased FROM
products GROUP BY productcategory, datepurchased;