Criação de uma guia cruzada no MySQL

Os dados armazenados em um banco de dados geralmente também são úteis para fins estatísticos. Se você possui uma loja virtual, deseja poder criar um relatório sobre o faturamento. Você pode obter informações estatísticas usando GROUP BY, por exemplo.

SELECT DATE_FORMAT(invoice.date, '%M') AS `month`, COUNT(*) AS `invoice_count`, SUM(`invoice`.`amount`) AS `turnover`
FROM `invoice`
WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY MONTH(`invoice`.`date`)

month     invoice_count  turnover
January   84             9532.26
February  141            20857.61
March     91             10922.71
April     112            15044.48
May       101            9676.60 
June      137            12860.88
July      281            34291.20
August    191            26377.66
September 103            16324.78
October   99             12873.23

Se você está vendendo uma grande variedade de produtos, talvez queira ver o volume de negócios de cada categoria de produto. Você poderia fazer isso com um simples GROUP BY como:

SELECT DATE_FORMAT(`invoice`.`date`, '%M') AS `month`, `category`.`description` AS `category`, COUNT(*) AS `product_count`, SUM(`invoice_product`.`amount`) AS `turnover`
FROM `invoice` INNER JOIN `invoice_product` ON `invoice`.`id` = `invoice_product`.`invoice_id` LEFT JOIN `product` ON `invoice_product`.`product_id` = `product`.`id` LEFT JOIN `category` ON `product`.`category_id` = `category`.`id`
WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31' 
GROUP BY MONTH(`invoice`.`date`), `category`.`id`

month     category   product_count  turnover   
January   Hardware   62             4821.31   
January   Software   51             4419.41   
January   Cables     12             291.54   
February  Hardware   71             8408.93   
February  Software   101            11726.36   
February  Cables     17             312.32   
February  Other      2              410.00   
March     Hardware   21             2371.58   
March     Software   81             8238.81    
March     Cables     13             312.32   
... 

Isso daria a você cada categoria em uma linha diferente, ordenada por mês. Embora contenha todas as informações, o formato está longe de ser bom. Em vez disso, você gostaria de ter 1 linha por mês com cada categoria como uma coluna e também as informações sobre as faturas.

SELECT DATE_FORMAT(`invoice`.`date`, '%M') AS `month`, COUNT(DISTINCT `invoice`.`id`) AS `product_count`, COUNT(*) AS `invoice_count`, SUM(`invoice_product`.`amount`) AS `turnover`,
  SUM(`product`.`category_id`=1) AS `hardware_count`, SUM(IF(`product`.`category_id`=1, `invoice_product`.`amount`, 0)) AS `hardware_turnover`,
  SUM(`product`.`category_id`=2) AS `software_count`, SUM(IF(`product`.`category_id`=2, `invoice_product`.`amount`, 0)) AS `software_turnover`,
  SUM(`product`.`category_id`=3) AS `cables_count`, SUM(IF(`product`.`category_id`=3, `invoice_product`.`amount`, 0)) AS `cables_turnover`,
  SUM(`product`.`category_id`=4) AS `other_count`, SUM(IF(`product`.`category_id`=4, `invoice_product`.`amount`, 0)) AS `other_turnover`
FROM `invoice` INNER JOIN `invoice_product` ON `invoice`.`id` = `invoice_product`.`invoice_id` LEFT JOIN `product` ON `invoice_product`.`product_id` = `product`.`id` LEFT JOIN `category` ON `product`.`category_id` = `category`.`id`
WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY MONTH(`invoice`.`date`), `category`.`id`

month     invoice_count  turnover    hardware_count  hardware_turnover  software_count  software_turnover  cables_count  cables_turnover  other_count  other_turnover
January   84             9532.26     62              4821.31            51              4419.41            12            291.54           0            0
February  141            20857.61    71              8408.93            101             11726.36           17            312.32           2            410.00
March     91             10922.71    21              2371.58            81              8238.81            13            312.32           0            0
...

A grande desvantagem desse método é que você precisa modificar a consulta se uma categoria for adicionada. Isso pode ser resolvido criando dinamicamente a consulta em PHP / Ruby / Python / Node.js / etc.