Percentage of a 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 2000 Forums

Transact-SQL (2000)

Percentage of a Total

Author

Topic

Leigh79
Starting Member

28 Posts

Posted - 2008-03-12 : 07:55:10

Hi Guys

Can you help me with this? I am trying to find out the percentage each row's commission is of the total commission.

My Query is:

SELECT tbl1.tar_seasoncode, SUM(tbl1.tar_opcommoninvoice), SUM(tbl1.tar_opcommoninvoice)/SUM(tbl2.tot)
FROM tar_downloads_new AS tbl1,
( SELECT SUM(tar_opcommoninvoice) AS tot FROM tar_downloads_new WHERE (tar_download_filename LIKE '%BA733436%') ) tbl2
WHERE (tar_download_filename LIKE '%BA733436%')
GROUP BY tar_seasoncode

The error message I am getting is - Column 'tbl2.tot' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Where am I going wrong?

Thanks

Leigh79
Starting Member

28 Posts

Posted - 2008-03-12 : 08:08:03

Sorry, I've just realised what an idiot I've been, I have now added 'tbl2.tot' into the GROUP Clause!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:10:34

[code]DECLARE @Sample TABLE (t INT, v INT)

INSERT @Sample
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 4

SELECT x.t,
x.v,
100.0 * x.v / p.v
FROM (
SELECT t,
SUM(v) AS v
FROM @Sample
GROUP BY t
) AS x
CROSS JOIN (
SELECT SUM(v) AS v
FROM @Sample
) AS p[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources