Display Percentage of grouped SUM to total SUM

Question

I currently have results like

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

Using columns from tables

Invoice_Itemized

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

Invoice_Totals

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

Inventory

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

Departments

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

with the following code

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

I would like results like

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

The problem I encounter is that I'm trying to divide a the grouped results of a SUM-SUM by the total of the same SUM-SUM. I've tried something similar to the suggestion made in

Percentage from Total SUM after GROUP BY SQL Server

but that didn't seem to work for me. I was getting binding errors. Any suggestions?

_Why_ didn't it work? What did you get instead? Side note: Please explicitly list out your `JOIN`s, and put as many conditions in the `ON` clause as possible, rather than using the comma-separated `FROM` clause, it's much easier to read and reason about. — Clockwork-Muse, Nov 10 '18 at 00:54
*Never* use commas in the `FROM` clause. *Always* use proper, explicit, **standard** `JOIN` syntax. — Gordon Linoff, Nov 10 '18 at 12:59

score 0 · Answer 1 · answered Nov 10 '18 at 02:09

This should work:

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

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ']'. Msg 102, Level 15, State 1, Line 19 Incorrect syntax near 'q'. Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'q1'. — Yofi, Nov 10 '18 at 16:15
@RedDevil i missed a square bracket :) q.[Total Profit]/q1.[Total Profit] — ujawg, Nov 10 '18 at 16:17

score 0 · Accepted Answer · answered Nov 10 '18 at 13:01

0

You can do this with window functions:

with t as (
      <your query here>
     )
select t.*,
       profit * 100.0 / sum(profit) over () as profit_percentage
from t;

answered Nov 10 '18 at 13:01

Gordon Linoff

1,242,037
58
646
786

Display Percentage of grouped SUM to total SUM

2 Answers2