MySQL Staging statistics

Product table

CREATE TABLE ' Product ' (

' product_id ' int (one) not NULL auto_increment,

' Product_model ' varchar (255) is not NULL,

' Product_price ' decimal (15,4) is not NULL,

' Product_status ' tinyint (1) unsigned not NULL,

' Product_add_time ' int (one) unsigned DEFAULT ' 0 ',

PRIMARY KEY (' product_id '),

KEY ' Product_price ' (' Product_price ') USING BTREE

) Engine=innodb auto_increment=24 DEFAULT charset=utf-8
Classification table

CREATE TABLE ' category ' (

' category_id ' int (one) unsigned not NULL auto_increment,

' category_name ' varchar (255) is not NULL,

PRIMARY KEY (' category_id ')

) Engine=innodb auto_increment=4 DEFAULT charset=utf-8
Product Classification Table

CREATE TABLE ' Product_to_category ' (

' product_id ' int (one) unsigned not NULL,

' category_id ' int (one) unsigned not NULL,

PRIMARY KEY (' product_id ', ' category_id ')

) Engine=innodb DEFAULT Charset=utf-8

To obtain product price range statistics on the shelves of the shoes category of products, the status of the number of sale/discontinued

SELECT

SUM (case if product.product_price<10 then 1 else 0 end) as ' (0,10) ',

SUM (case if product.product_price>=10 and product.product_price<20 then 1 else 0 end) as ' [10,20] ',

SUM (case if product.product_price>=20 and product.product_price<30 then 1 else 0 end) as ' [20,30] ',

SUM (case if product.product_price>=30 and product.product_price<40 then 1 else 0 end) as ' [30,40] ',

SUM (case if product.product_price>=40 then 1 else 0 end) as ' [40,+] '

From Test.product as product

Left JOIN test.product_to_category map on product.product_id = map.product_id

Left JOIN test.category category on map.category_id = category.category_id

WHERE category.category_name = ' Shoes ' GROUP by Product.product_status;

MySQL Staging statistics