Pfthb

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?

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;

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