20121025_1

gistfile1.sql

CREATE TABLE IF NOT EXISTS t_order (order_id int(5) NOT NULL AUTO_INCREMENT, product_id int(5) NOT NULL, order_date date NOT NULL, PRIMARY KEY (order_id)) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21;
INSERT INTO t_order (order_id, product_id, order_date) VALUES
(1, 1, '2012-09-01'),(2, 1, '2012-09-01'),(3, 2, '2012-09-01'),(4, 3, '2012-09-03'),(5, 3, '2012-09-03'),
(6, 2, '2012-09-03'),(7, 1, '2012-09-11'),(8, 2, '2012-09-11'),(9, 1, '2012-09-14'),(10, 3, '2012-09-15'),
(11, 1, '2012-09-16'),(12, 2, '2012-09-16'),(13, 2, '2012-09-16'),(14, 3, '2012-09-16'),(15, 3, '2012-09-16'),
(16, 3, '2012-09-16'),(17, 1, '2012-09-20'),(18, 2, '2012-09-20'),(19, 3, '2012-09-30'),(20, 2, '2012-10-01');

CREATE TABLE IF NOT EXISTS t_product ( product_id int(5) NOT NULL AUTO_INCREMENT, product_name varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (product_id)) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
INSERT INTO t_product (product_id, product_name) VALUES (1, 'プランA'), (2, 'プランB'), (3, 'プランC');

select t_order.order_date as 日付
, sum(case t_product.product_id when 1 then 1 else 0 end) as プランA
, sum(case t_product.product_id when 2 then 1 else 0 end) as プランB
, sum(case t_product.product_id when 3 then 1 else 0 end) as プランC
, count(1) as 合計
from t_order
inner join t_product
on t_order.product_id = t_product.product_id
group by t_order.order_date;