I would like to divide the quarterly product sales results into 4 columns (one for each quarter). My table is as follows:
CREATE TABLE `tcustomerscontracts` (
`contract_id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL DEFAULT '0',
`contract_quantity` decimal(8,2) NOT NULL DEFAULT '0.00',
`contract_date_from` varchar(10) NOT NULL,
`customer_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`contract_id`),
KEY `PRODUCT` (`product_id`),
KEY `CUSTOMER` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Applying the following query:
SELECT `tcustomerscontracts`.`contract_id`, YEAR(`tcustomerscontracts`.`contract_date_from`) AS forecast_year,`tproducts`.`product_id`,`tproducts`.`product_name`,SUM(contract_quantity) AS forecast_quantity,QUARTER(`tcustomerscontracts`.`contract_date_from`) AS forecast_quarter FROM `tcustomerscontracts`
INNER JOIN `tproducts` ON `tproducts`.`product_id` = `tcustomerscontracts`.`product_id`
GROUP BY YEAR(`tcustomerscontracts`.`contract_date_from`),`tproducts`.`product_name`,QUARTER(`tcustomerscontracts`.`contract_date_from`)
ORDER BY YEAR(`tcustomerscontracts`.`contract_date_from`) DESC, `tproducts`.`product_name` ASC, QUARTER(`tcustomerscontracts`.`contract_date_from`) ASC;
... I get a grouping by quarter:
While I would like this result (unfortunately only obtained via code):
Thanks in advance
Thanks to @P.Salmon I tried to use the suggestion indicated and I believe I am on the right path even if it is still incomplete.
My new query is:
SELECT
YEAR(`contract_date_from`) as YEAR,product_id AS PROD_ID,
CASE WHEN (quarter(contract_date_from)=1) THEN SUM(contract_quantity) ELSE 0 END AS Q1,
CASE WHEN (quarter(contract_date_from)=2) THEN SUM(contract_quantity) ELSE 0 END AS Q2,
CASE WHEN (quarter(contract_date_from)=3) THEN SUM(contract_quantity) ELSE 0 END AS Q3,
CASE WHEN (quarter(contract_date_from)=4) THEN SUM(contract_quantity) ELSE 0 END AS Q4
FROM tcustomerscontracts
where customer_id=2
GROUP BY YEAR(`contract_date_from`) DESC, `product_id` ASC,QUARTER(`tcustomerscontracts`.`contract_date_from`) ASC;
and the result is the following:
The results should appear as indicated by the red arrows. Instead, a new row is created in the transition from quarter to quarter.