Complicate SQL question...?? anyone know? urgent~~

campbells

I not sure is there way to show percentage of my sales. I have done a SQL on showing the total sales and now i want to calculate the percentage of total for each department. Example TOTAL sales= $100 Vendor1 =$20 Vendor2 =$10 ........ how can correctly show another column that will calculate the percentage?? I have try many method but not working... i was thinking to write as sum(ti.totalsales)/sum(sum(it.totalsales))*100 as precentage is SQL able to support those complicated calculation? or i have to do it in vb... ---HEre is the basic show the total sales with out percentage... select d.deptdesc,v.[name],ti.deptcode ,sum(ti.totalsales) as Total from tot_item ti , dept d, item i, vendor v where ti.itemcode=i.itemcode and v.vendorid=i.vendorid and ti.deptcode=d.deptcode group by v.[name], ti.deptcode, d.deptdesc ORDER BY v.[name], ti.deptcode ASC

Michael Potter

The sample query you list has both Department and Vendor in it (you could get departments listed more than once if they use different vendors). You are trying to get a percentage of total for each department but your sample result set lists no percentages and values are listed by vendor. Are you sure you know what you really need? Given your 2nd line and assuming you are using SQL Server, first calculate a good total sales per department. SELECT d.deptdesc, SUM(ti.totalsales) AS DeptTotal FROM tot_item ti INNER JOIN dept d ON (ti.deptcode=d.deptcode) Now write a query that calculates total sales.SELECT SUM(ti.totalsales) AS FullTotal FROM tot_item ti Now put them together SELECT dep.deptdesc, (dep.DeptTotal / (SELECT SUM(ti.totalsales) FROM tot_item ti)) * 100 AS PercentOfSales FROM (SELECT d.deptdesc, SUM(ti.totalsales) AS DeptTotal FROM tot_item ti INNER JOIN dept d ON (ti.deptcode=d.deptcode)) AS dep ORDER BY deptdesc Note: I am using a more modern form of the JOIN syntax. There are issues with expressing the JOIN in the WHERE clause. You can make this a lot cleaner using variables in a stored proc.

Michael Potter

Sorry - forgot the GROUP BY clause in the aggregate:

SELECT     
    d.deptdesc,    
    SUM(ti.totalsales) AS DeptTotal 
FROM     
    tot_item ti 
INNER JOIN     
    dept d    
    ON (ti.deptcode=d.deptcode)
GROUP BY
    deptdesc

This will also need to be modified in the final query.

ednrgc

You can try something like this: DECLARE @TempTotal int SELECT @TempTotal=sum(ti.totalsales) from ti select d.deptdesc,v.[name],ti.deptcode ,(sum(ti.totalsales)/@TempTotal) as Total from tot_item ti, dept d, item i, vendor v where ti.itemcode=i.itemcode and v.vendorid=i.vendorid and ti.deptcode=d.deptcode group by v.[name], ti.deptcode, d.deptdesc ORDER BY v.[name], ti.deptcode ASC