Percentage relative to Total - 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 2005 Forums

Transact-SQL (2005)

Percentage relative to Total

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-02 : 07:31:31

I'm an occasional SQL User and need to place the Select Expression in an ASPX page.

I have two queries, one in order to get the total; another to get the total by each Catergory. In order to get the percentage for each of the categories in relation to the total I guess I have to link/join these Queries, but I dont know how.

SELECT dbo.Debts.Debt, sum (dbo.Debts.Amount) as Total_by_Category
FROM dbo.Debts
GROUP BY dbo.Debts.Debt

SELECT sum (dbo.Debts.Amount) as Total
FROM dbo.Debts
GROUP BY dbo.Debts.ALL

This is a simplified reduction of the Query; I'm working with a complicated WHERE expression with lots of @parameters I pass through the aspx page. If it wasn't for these @parameters I would have created a seperate view for the total and than relate to that one.

Any hint is apreciated.
Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 07:58:45

Maybe you can cross join the value from your "select the total"

See my example:

select 
cat,subtotal,total, subtotal/(total/100.00) as perc 
from
 (select 1 as cat,25 as subtotal
  union all 
  select 2 as cat,75 as subtotal) 
as sub
cross join 
(select 200 as total) total

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 08:42:29

[code]DECLARE @Sample TABLE
(
Code CHAR(1),
i INT
)

INSERT @Sample
SELECT 'A', 5 UNION ALL
SELECT 'A', 10 UNION ALL
SELECT 'B', 20

SELECT Code,
SUM(i) AS GroupSum,
MAX(t) AS TotalSum,
1.0E * SUM(i) / MAX(t) AS GroupAverage
FROM (
SELECT Code,
i,
SUM(i) OVER (PARTITION BY (SELECT 0)) AS t
FROM @Sample
) AS d
GROUP BY Code[/code]

E 12°55'05.63"
N 56°04'39.26"

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-04 : 11:22:29

Thank you Peso, this works fine and is damn elegant, although it took a while to understand where to place the WHERE expression.

SELECT Code,
SUM(i) AS GroupSum,
MAX(t) AS TotalSum,
1.0E * SUM(i) / MAX(t) AS GroupAverage
FROM (
SELECT Code,
i,
SUM(i) OVER (PARTITION BY (SELECT 0)) AS t
FROM @Sample WHERE...
) AS d
GROUP BY Code

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 11:37:51

removing the partition by leaving a empty over() also works


SELECT		Code,
		SUM(i) AS GroupSum,
		MAX(t) AS TotalSum,
		1.0E * SUM(i) / MAX(t) AS GroupAverage
FROM		(
			SELECT	Code,
				i,
				SUM(i) OVER ( PARTITION BY (SELECT 0) ) AS t
			FROM	@Sample
		) AS d
GROUP BY	Code

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 11:51:27

Even in SQL Server 2008?

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 12:34:11

quote:
Originally posted by Peso

Even in SQL Server 2008?

E 12°55'05.63"
N 56°04'39.26"

don't have SQL Server 2008 now . . will try that tomorrow

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 13:08:51

I have 2008 and the OVER () didn't work in earlier versions (beta).
So I have been used to write OVER (PARTITION BY (SELECT 0)), because it worked in beta, RTM and SP1.
Maybe OVER() do work in RTM and sp1?

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 21:10:53

just tested on Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

it works with over()

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-05 : 00:59:08

Great! Thank you.

E 12°55'05.63"
N 56°04'39.26"