percent calculation on returned values

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

General SQL Server Forums

New to SQL Server Programming

percent calculation on returned values

Author

Topic

spinningtop
Starting Member

29 Posts

Posted - 2012-01-10 : 07:41:59

Hi
I want to create a script to return 2 values. The first value comes from running just the first 2 lines below. The second value comes from
including the innerjoin with another table, ie running the whole query below. I then want the script to give me a final value of the second value as a percentage of the first.

Any pointers would be appreciated.



  SELECT SUM(items) AS countTable1
  FROM Table1
  
INNER JOIN
  Table2.items ON table1.items = table2.items

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-10 : 07:51:29

[code]
SELECT SUM(table2.items) * 100.0 /
SUM(table1.items)
FROM Table1
LEFT JOIN Table2.items ON table1.items = table2.items
[/code]

KH
[spoiler]Time is always against us[/spoiler]

spinningtop
Starting Member

29 Posts

Posted - 2012-01-10 : 08:18:25

Thanks for looking at this.
Sorry for this but I made a mistake in the original query.

To clarity, there are two tables. Table1 with two columns, an item description column (items) and a number of items (numOfItems). table2 has just an item description column and is used to match to in the innerjoin.

So as you see, I want to return two integer values. One from running the first 2 lines of the query and the second from the whole query and find the percentage of them.

SELECT SUM(numOfItems) AS sumNumofItems
FROM Table1

INNER JOIN
Table2.items ON table1.items = table2.items

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-10 : 08:55:13

[code]
SELECT SUM(t1.numOfItems) AS sumNumofItems,
SUM(case when t2.items is not null then t1.numOfItems else 0 end) * 100.0 / SUM(t1.numOfItems)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.items = t2.items
[/code]

KH
[spoiler]Time is always against us[/spoiler]

spinningtop
Starting Member

29 Posts

Posted - 2012-01-11 : 11:10:21

Thanks I went down the route of writing the output to a variable in the end. though I'm sure your method is probably better.


DECLARE @variable1 DECIMAL
DECLARE @variable2 DECIMAL

SELECT @variable1 =   SELECT SUM(numOfItems) AS sumNumofItems
FROM Table1
INNER JOIN
Table2.items ON table1.items = table2.items
                   
               
SELECT @variable2 =   SELECT SUM(numOfItems) AS sumNumofItems
FROM Table1
                   
PRINT (@variable1 / @variable2) *100
                 
             GO

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 11:12:45

Did you TRY Mr. Tan's suggestion?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources