Percentage by category - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Percentage by category

Author

Topic

de9625
Starting Member

17 Posts

Posted - 2002-04-17 : 10:04:29

I have a little problem. I have one table with transactions in it. Each transaction has a date,amount and a category. I want to print the category and the percentage of the total amount in each category, something like this:

Category Amount(%)
-----------------------
A 10
B 70
C 20

If I do a simple SQLstmt:
SELECT Category, SUM(Amount) AS "Amount (USD)"
FROM Table_1
GROUP BY Category

Then I will get the categories with the total amount for each category. If I do another SQLStmt:
Select SUM(Amount)
From Table_1

Then I will get the total amount of all categories.
But how do I write a SQLStmt where I divied the amount for each category with the total amount for all categories and multiply the result with 100 so I'll get the percentage, like the print out above?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 10:09:50

Add a subquery that finds the total SUM, then refer to it in the main query:

SELECT Category, SUM(Amount) AS "Amount (USD)", Sum(Amount)/S.Total AS Percentage
FROM Table_1, (Select SUM(Amount) AS Total From Table_1) S
GROUP BY Category

If Amount is an integer column, you may need to use CONVERT() in order to get decimal places.

Nazim
A custom title

1408 Posts

Posted - 2002-04-17 : 10:10:08

select customerkey,sum(amount)/(select sum(amount)
from table1)
from table1
group by category

HTH

EDIT: Snipers on the prowl

Edited by - Nazim on 04/17/2002 10:12:21

de9625
Starting Member

17 Posts

Posted - 2002-04-17 : 12:02:13

Hmmm..I cant get it to work!

I use WinSQL and try to query a db2(AS400)

Using this SQLStmt:
"SELECT Category, SUM(Amount) AS "Amount (USD)", Sum(Amount)/S.Total AS Percentage
FROM Table_1, (Select SUM(Amount) AS Total From Table_1) S
GROUP BY Category"

I get the message:
Error: SQL0122 - Column TOTAL or expression specified in SELECT list not valid. (State:S1000, Native Code: FFFFFF86)

And if I try the other SQLStmt I get this msg:
Error: SQL0104 - Token SUM was not valid. Valid tokens: + ) -. (State:37000, Native Code: FFFFFF98)

"Amount" is a decimal column.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 12:47:52

quote:
Hmmm..I cant get it to work!

I use WinSQL and try to query a db2(AS400)

Well, you didn't SAY you were using DB2 or AS/400, and if they don't support subqueries like this, then it won't work. It DOES work in SQL Server (which is what SQL Team focuses on)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-17 : 12:55:30

why not break the query up into 2 parts.....

part 1
- Select SUM(Amount) AS Total From Table_1 and save result in @var1

part 2
- SELECT Category, SUM(Amount) AS "Amount (USD)", Sum(Amount)/ @var1 AS Percentage
FROM Table_1 GROUP BY Category

probably not efficient, but should work...it's a trade off!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources