Query for percentage of a SUM

[Ben]

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

[--CELKO--]

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;

[Hugo Kornelis]

On 13 Dec 2004 17:16:39 -0800, Ben wrote:

All seems to calculate with the exception of the Percent where all the
percentages 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)

[Erland Sommarskog]

[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