Thursday, January 28, 2010

PIVOT with MySQL

DROP TABLE IF EXISTS tbl_sales_revenue;
CREATE TABLE tbl_sales_revenue(dept_id int, year int, revenue double);
INSERT INTO tbl_sales_revenue(dept_id, year, revenue)
VALUES
(1,2007,1200),
(1,2008,1000),
(1,2009,2000),
(2,2007,3000),
(2,2008,4000),
(2,2009,5000),
(3,2007,500),
(3,2008,1200),
(3,2009,1500);

SELECT
IFNULL(dept_id,'Total') AS 'Department',
a.2007, a.2008, a.2009,
a.2007 + a.2008 + a.2009 AS Total
FROM (
SELECT
dept_id,
SUM(IF(year=2007, revenue, 0)) As '2007',
SUM(IF(year=2008, revenue, 0)) As '2008',
SUM(IF(year=2009, revenue, 0)) As '2009'
FROM tbl_sales_revenue
GROUP BY dept_id WITH ROLLUP
) AS a;

No comments:

Post a Comment

Oracle Query