mysql - 在MySql中计算百分比

Question

我想根据表中特定产品的金额计算百分比。我想根据下表的计算添加百分比列。表列是:

Item_Category : It gives the item name
Item_Amount : It gives the sum of the particular item

我使用的查询是:

select Item_Category,
      (SUM(Item_Amount)/100000) as Total,
      quarter(Invoice_date) as Quarter,
      year(Invoice_date) as Year 
from `cmpsldata` 
where Site='BDD1' 
      and Item_code LIKE 'FG%' 
      and Invoice_type='Excise'    
group by Year,Item_Category,Quarter 
order by Total desc

Table : 
| Item_Category | Total | Quarter | Year |
| Product A     | 78.65 | 1       | 2015 |
| Product B     | 65.54 | 1       | 2015 |
| Product C     | 45.78 | 1       | 2015 |

这是当前的表格，现在我需要再添加 1 列，它可以计算产品 A、B、C(单独)的百分比。

Answer 1

在 MySQL 支持 SUM() OVER(...) 等窗口函数之前，需要使用子查询来收集百分比计算所需的额外数据。

不太确定您需要什么，但希望这会有所帮助:

SELECT
            i.Item_Category
          , i.Total
          , i.Quarter
          , i.Year
          , (i.Total * 100.0) / y.Total as Pct
FROM (
      SELECT
            Item_Category
          , (SUM(Item_Amount) / 100000) AS Total
          , quarter(Invoice_date) AS Quarter
          , YEAR(Invoice_date) AS Year
      FROM `cmpsldata`
      WHERE Site = 'BDD1'
            AND Item_code LIKE 'FG%'
            AND Invoice_type = 'Excise'
      GROUP BY Year
             , Item_Category
             , Quarter
      ) i
      INNER JOIN (
            SELECT
                  (SUM(Item_Amount) / 100000) AS Total
                , quarter(Invoice_date) AS Quarter
                , YEAR(Invoice_date) AS Year
            FROM `cmpsldata`
            WHERE Site = 'BDD1'
                  AND Item_code LIKE 'FG%'
                  AND Invoice_type = 'Excise'
            GROUP BY Year
                   , Quarter
      ) y ON i.year = y.year
                  AND i.quarter = y.quarter
ORDER BY i.Total DESC