Xtykutl

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