时间戳

首页 > 解决方案 > 显示分组 SUM 占总 SUM 的百分比

sql - 显示分组 SUM 占总 SUM 的百分比

问题描述

我目前有类似的结果

total sales | total cost | total profit | department
----------------------------------------------------
100          50            50             A
80           20            60             B
250          120           130            C

使用表中的列

Invoice_Itemized

itemnum | costper | priceper | quantity | invoice_number
--------------------------------------------------------

发票_总计

invoice_number | datetime
---------------------------

存货

itemnum | dept_id
------------------

部门 

 dept_id | description  
 ----------------------

使用以下代码

select sum(invoice_itemized.priceper* invoice_itemized.quantity) as "Total Sales",

sum(invoice_itemized.quantity*inventory.cost) as "Total Cost", 

sum(invoice_itemized.priceper* invoice_itemized.quantity)- 
sum(invoice_itemized.quantity*inventory.cost) as "Total Profit", 

departments.description as Department

from invoice_itemized, invoice_totals, inventory, departments

where invoice_itemized.invoice_number=invoice_totals.invoice_number

and year(invoice_totals.datetime)=2018 and month(invoice_totals.datetime)=10

and inventory.itemnum=invoice_itemized.itemnum 

and inventory.dept_id=departments.dept_id

and departments.description<>'shop use'

and departments.description<>'none'

and departments.description<>'ingredients'

group by departments.description

order by "total profit" desc

我想要这样的结果

total sales | total cost | total profit | percentage total profit | department
-------------------------------------------------------------------------------
100          50            50                     20.83                 A
80           20            60                      25                   B
250          120           130                    54.17                 C

我遇到的问题是我试图将 SUM-SUM 的分组结果除以相同 SUM-SUM 的总数。我已经尝试过类似于在

GROUP BY SQL Server 后总和的百分比

但这似乎对我不起作用。我遇到了绑定错误。有什么建议么？

标签： sqlsql-servertsql

解决方案

这应该有效：

Select q.[Total Sales],
    q.[Total Cost],
    q.[Total Profit],
    q.Total Profit] / q1.Total Profit] as [Percentage Total Profit],
    q.Department
from (
    select sum(invoice_itemized.priceper* invoice_itemized.quantity) as [Total Sales],
        sum(invoice_itemized.quantity*inventory.cost) as [Total Cost], 
        sum(invoice_itemized.priceper* invoice_itemized.quantity) - sum(invoice_itemized.quantity*inventory.cost) as [Total Profit], 
        departments.description as Department
    from invoice_itemized, invoice_totals, inventory, departments
    where invoice_itemized.invoice_number=invoice_totals.invoice_number
        and year(invoice_totals.datetime)=2018 and month(invoice_totals.datetime)=10
        and inventory.itemnum=invoice_itemized.itemnum 
        and inventory.dept_id=departments.dept_id
        and departments.description<>'shop use'
        and departments.description<>'none'
        and departments.description<>'ingredients'
    group by departments.description) q
join (
    select sum(t.[Total Profit]) as [Total Profit]
    from (select sum(invoice_itemized.priceper* invoice_itemized.quantity) as [Total Sales],
        sum(invoice_itemized.quantity*inventory.cost) as [Total Cost], 
        sum(invoice_itemized.priceper* invoice_itemized.quantity) - sum(invoice_itemized.quantity*inventory.cost) as [Total Profit], 
        departments.description as Department
    from invoice_itemized, invoice_totals, inventory, departments
    where invoice_itemized.invoice_number=invoice_totals.invoice_number
        and year(invoice_totals.datetime)=2018 and month(invoice_totals.datetime)=10
        and inventory.itemnum=invoice_itemized.itemnum 
        and inventory.dept_id=departments.dept_id
        and departments.description<>'shop use'
        and departments.description<>'none'
        and departments.description<>'ingredients'
    group by departments.description) t
) q1 on q1.[Total Profit] = q1.[Total Profit]
order by q.[Total Profit] desc

推荐阅读