How write mysql query corresponding this tables This is my database tables


I want to get this result:



How can I write select query to get that result?
You have to use dynamic SQL (PREPARE, EXECUTE) for that

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(
  'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ', 
  DATE_FORMAT(date, '%Y%m'), 
  ' THEN op.count ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Count`,',
  'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ', 
  DATE_FORMAT(date, '%Y%m'), 
  ' THEN op.count * op.price ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Sum`'))
  INTO @sql
  FROM `order`;

SET @sql = CONCAT(
  'SELECT c.name,p.title,', @sql,
  '  FROM `order` o JOIN order_product op 
       ON o.id = op.order_id JOIN client c
       ON o.client_id = c.id JOIN product p
       ON op.product_id = p.id
    GROUP BY o.client_id, c.name, op.product_id, p.title');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


Here is SQLFiddle demo



To simplify things on the calling end you can wrap it up into a stored procedure like this

DELIMITER $$
CREATE PROCEDURE sp_monthly_report()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ', 
    DATE_FORMAT(date, '%Y%m'), 
    ' THEN op.count ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Count`,',
    'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ', 
    DATE_FORMAT(date, '%Y%m'), 
    ' THEN op.count * op.price ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Sum`'))
    INTO @sql
    FROM `order`;

  SET @sql = CONCAT(
    'SELECT c.name,p.title,', @sql,
    '  FROM `order` o JOIN order_product op 
         ON o.id = op.order_id JOIN client c
         ON o.client_id = c.id JOIN product p
         ON op.product_id = p.id
      GROUP BY o.client_id, c.name, op.product_id, p.title');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;


Sample usage:

CALL sp_monthly_report();


Here is SQLFiddle demo