438,843 Members | 2,300 Online Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

# Query for percentage of a SUM

 P: n/a I am trying to figure out the syntax for a query that will essentially give me the Percentage each of my areas contributes to the Whole. I know this can be achieved by multiple queries but I would like to keep it intact as one single query if possible. For Example I have the following data set-- AREA MOU NE 1234 SO 4312 WE 12312 MW 97123 NE 1123 SO 31 WE 312 MW 971 The results I would like to see would look like AREA MOU PERCENT MW 98094 .83536 NE 2357 .02007 WE 12624 .10751 SO 4352 .03706 The query I came up with is-- SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, sum(MOU) / (SELECT SUM(MOU) AS TOTAL_MOU FROM [2004_NOVEMBER_COST])as [PERCENT] FROM [2004_NOVEMBER_COST] GROUP BY Area All seems to calculate with the exception of the Percent where all the percentages are 0's. I think I need to take the first line AREA_TOTAL and now divide by the SUM(MOU) like this-- SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, AREA_TOTAL / (SELECT SUM(MOU) AS TOTAL_MOU but I get Invalid Column. I essence I think it is a simple query but I am hitting a wall. Any advice would help. Thanks, Ben Jul 23 '05 #1
3 Replies

 P: n/a Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. Sample data is also a good idea, along with clear specifications. You might also stop putting numbers at the front of names and using proprietary square brackets. It looks like you are using one table per month per year!! That would certainly not be the case in a properly designed database; that would be serious attribute splitting. Try qualifying the names better, something like this. Without DDL, this is a wild guess: SELECT A1.area, SUM(A1.mou) AS area_total, (SUM (A1.mou) / (SELECT SUM (A2.mou) FROM AreaCosts AS A2 WHERE A2.report_month = 11 ) ) AS percentage FROM AreaCosts AS A1 WHERE A1.report_month = 11 GROUP BY A1.area; Jul 23 '05 #2

 P: n/a On 13 Dec 2004 17:16:39 -0800, Ben wrote: All seems to calculate with the exception of the Percent where all thepercentages are 0's. Hi Ben, I didn't study your query in detail, but I guess that it's correct (though you don't need the DISTINCT if you already do GROUP BY!). The reason the percentages are 0 is because SQL Server will use integer division (both operands of the / operator are integer), discarding the fractional part. Try running these: SELECT 7 / 8 SELECT 7 / 8.0 SELECT 7.0 / 8 As you see, making sure that at least one of the operands is not integer is enough to get an exact result. To get the same effect in your query, just change "sum(MOU) / ..." to "CAST(sum(MOU) AS numeric) / ..." Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Jul 23 '05 #3

 P: n/a [posted and mailed, please reply in news] Ben (wi*****@comcast.net) writes: All seems to calculate with the exception of the Percent where all the percentages are 0's. I think I need to take the first line AREA_TOTAL and now divide by the SUM(MOU) like this-- SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, AREA_TOTAL / (SELECT SUM(MOU) AS TOTAL_MOU but I get Invalid Column. You cannot use a column alias later in the query. Just imagine the table you are querying actually have an AREA_TOTAL column. What would happen then? This may be the best way to write the query. SELECT Area, SUM(MOU) AS Area_total, 1.0 * SUM(MOU) / x.grand_total FROM tbl CROSS JOIN (SELECT grand_total = SUM(MOU) FROM tbl) AS x GROUP BY Area -- Erland Sommarskog, SQL Server MVP, es****@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp Jul 23 '05 #4

### This discussion thread is closed

Replies have been disabled for this discussion. 