0

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: My query result

While I would like this result (unfortunately only obtained via code): enter image description here

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:

enter image description here

The results should appear as indicated by the red arrows. Instead, a new row is created in the transition from quarter to quarter.

oguz ismail
  • 1
  • 16
  • 47
  • 69
Roby72
  • 11
  • 4

0 Answers0