-1

How to calculate percentage with a SQL statement

I have a similar question to this except for revenue. A corporation has a region (east west central) and a revenue

I've aggregated the regions but i'm having trouble dealing with the percents, this is what i have so far

  • Summary of revenues by region (in ranked order from highest to lowest, calculate % of total for each region)

here's my code so far

SELECT Region, Sum(revenue) AS TotalRevenue
FROM Sales
GROUP BY Region
ORDER BY Sum(revenue) DESC;

i've also tried

SELECT Sales.Region, Sales.Sales.[*] AS Expr1
FROM Sales
GROUP BY Sales.Sales.[*], Sales;

i'm still very confused

I tried this code, and it worked SELECT Region , Sum(revenue) AS TotalRevenue , Sum(revenue) / (SELECT SUM(revenue) FROM Sales) * 100 AS percentage FROM Sales GROUP BY Region ORDER BY Sum(revenue) DESC;

Now, there's another part

For the region with the smallest revenue, show the products by revenue and % of regional sales For the region with the smallest revenue, show the customers list ranked by revenue and % of regional sales and then I have to prepare graphs or talbes to summarize my findings in excel should i just copy and paste my data into excel or link it to excel/import it into excel? if i need to do that ,how would i do that? thanks

Erik A
  • 31,639
  • 12
  • 42
  • 67
Fil Ott
  • 1
  • 4
  • What is `Sales.Sales.[*]` supposed to mean? – PM 77-1 Nov 16 '14 at 05:01
  • 2
    Please do not mangle the question. In order to be useful to anyone else, you must preserve the question as it was when Abecee answered. Thank you for your cooperation. – HansUp Nov 16 '14 at 18:29

1 Answers1

2
SELECT
  Region
  , Sum(revenue) AS TotalRevenue
  , Sum(revenue) / (SELECT SUM(revenue) FROM Sales) * 100 percentage
FROM Sales
GROUP BY Region
ORDER BY Sum(revenue) DESC;
Abecee
  • 2,365
  • 2
  • 12
  • 20
  • Where do you need the percent sign? Inside the column holding the value? This would require to change it to a character type - which would affect potential further processing. (SQL Fiddle did not complain about a missing `AS`, sorry.) – Abecee Nov 16 '14 at 06:24
  • ABC, it's solved, can you remove your answer or make it a blank/something else and i can give you approval if you do that, otherwise i won't – Fil Ott Nov 16 '14 at 18:23