Percentage - 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

Transact-SQL (2000)

Percentage

Author

Topic

michaeld
Starting Member

8 Posts

Posted - 2003-04-17 : 13:39:13

Category / Category Name / Hours
---------------------------------------------
23 / Windows / 23
24 / Blah Blah / 56
55 / Email / 34
45 / SQL / 67

I would like to add a column that shows the percentage hours and a subtotal of hours.

example:

Category / Category Name / Hours / % of hours
------------------------------------------------------------
23 / Windows / 23 / 13
24 / Blah Blah / 56 / 31
55 / Email / 34 / 19
45 / SQL / 67 / 37

Total hours
180

Here is what i have so far:
select problems.category, cname,(SUM(Time_spent)/60) AS 'Hours'
FROM Problems
JOIN Categories ON problems.category=categories.category_id
GROUP BY category,cname
ORDER BY 3

Any help is greatly appreciated.
michael

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-17 : 13:54:39

Here's one way. There must be a way to do this in one statement, but I could nto get it to work.

 
CREATE TABLE #Cat(Category INT, CatName VARCHAR(50), Hours INT)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(23,'Windows',23)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(24,'Blah Blah',56)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(55,'Email',34)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(45,'SQL',67)

DECLARE @TotalHours INT
SELECT @TotalHours = SUM(Hours) FROM #Cat

SELECT Category, Hours, @TotalHours AS TotalHours, ((CONVERT(DECIMAL(9,2), Hours)  / CONVERT(DECIMAL(9,2), @TotalHours)) * 100)
FROM #Cat

DROP TABLE #Cat

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

michaeld
Starting Member

8 Posts

Posted - 2003-04-17 : 14:08:55

Thanks for the reply MichaelP. Does someone have a suggestion for single statement?

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-17 : 14:22:07

I think the first way I did it is easier to read, but here's a single statement. The first way I showed you has a lower subtree cost and is easier to look at. I'd use the first one.

 
CREATE TABLE #Cat(Category INT, CatName VARCHAR(50), Hours INT)

INSERT INTO #Cat(Category, CatName, Hours) VALUES(23,'Windows',23)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(24,'Blah Blah',56)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(55,'Email',34)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(45,'SQL',67)

SELECT Category, Hours, (SELECT SUM(Hours) FROM #Cat) AS TotalHours, 
((CONVERT(DECIMAL(9,2), Hours)  / CONVERT(DECIMAL(9,2), (SELECT SUM(Hours) FROM #Cat))) * 100)
FROM #Cat

DROP TABLE #Cat

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

michaeld
Starting Member

8 Posts

Posted - 2003-04-17 : 14:39:05

How would I make this work if the categories and hours spent on each are dynamic numbers? would i still need to create a temp table? If so how would I use the code you have suggested with my original statement?

quote:

I think the first way I did it is easier to read, but here's a single statement. The first way I showed you has a lower subtree cost and is easier to look at. I'd use the first one.

CREATE TABLE #Cat(Category INT, CatName VARCHAR(50), Hours INT)

INSERT INTO #Cat(Category, CatName, Hours) VALUES(23,'Windows',23)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(24,'Blah Blah',56)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(55,'Email',34)
INSERT INTO #Cat(Category, CatName, Hours) VALUES(45,'SQL',67)

SELECT Category, Hours, (SELECT SUM(Hours) FROM #Cat) AS TotalHours,
((CONVERT(DECIMAL(9,2), Hours) / CONVERT(DECIMAL(9,2), (SELECT SUM(Hours) FROM #Cat))) * 100)
FROM #Cat

DROP TABLE #Cat

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-17 : 14:46:37

The temp table thing is just how I do my examples.

You basically need to apply the concepts that I showed you to your actual tables.

You you need something like this:

 
DECLARE @TotalHours INT
SELECT @TotalHours = (SUM(Time_spent)/60) FROM Problems 

SELECT problems.category, cname,
	(SUM(Time_spent)/60) AS 'Hours',
	((CONVERT(DECIMAL(9,2), (SUM(Time_spent)/60))  / CONVERT(DECIMAL(9,2), @TotalHours)) * 100) AS PercentOfHours
FROM Problems 
JOIN Categories ON problems.category=categories.category_id 
GROUP BY category,cname 
ORDER BY 3

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

michaeld
Starting Member

8 Posts

Posted - 2003-04-17 : 14:54:49

That is great, thanks! I a very new to SQL transact. One last question for today. How come this doesn't work "Compute SUM(percent)"?

The last column does not have a name so how can I compute a sum on it?

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-18 : 12:16:50

You don't need the function COMPUTE Here. Is used for a different context. Look it up in BOL.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources