SQL View - 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

General SQL Server Forums

New to SQL Server Programming

SQL View

Author

Topic

sathyarangaraj
Starting Member

11 Posts

Posted - 2012-09-25 : 03:35:42

I need to calculate Expr3=Expr1 / Expr 2 in SQL view. I have posted my code below. I am getting an error "The query cant be parsed"

SELECT (EXPR2 / Revenue)*100 FROM
(
SELECT
EXPR2= (SELECT SUM(CAST(A.[Total revenue] AS MONEY)) FROM dbo.tbl_Final_BRIT_Data A INNER JOIN dbo.tbl_contextgrouping AS B ON A.SYS_ID = B.Partner_ID
WHERE (A.[Total revenue] NOT LIKE '%[^0-9]%') GROUP BY B.Group_name),
REVENUE= (SELECT SUM(CAST(A.[Total revenue] AS MONEY)) FROM dbo.tbl_Final_BRIT_Data A INNER JOIN dbo.tbl_contextgrouping AS B ON A.SYS_ID = B.Partner_ID
WHERE (A.[Total revenue] NOT LIKE '%[^0-9]%') GROUP BY B.Group_name)
) AS EXPR3
Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-25 : 04:42:40

try

SELECT 	SUM(CAST(A.[Total revenue] AS MONEY)
	/ SUM(CAST(A.[Total revenue] AS MONEY) * 100
FROM 	dbo.tbl_Final_BRIT_Data AS A 
	INNER JOIN dbo.tbl_contextgrouping AS B 	ON 	A.SYS_ID = B.Partner_ID
WHERE 	(A.[Total revenue] NOT LIKE '%[^0-9]%') 
GROUP BY B.Group_name

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

sathyarangaraj
Starting Member

11 Posts

Posted - 2012-09-25 : 08:12:51

Thanks for your reply Khtan. I need to give condition in Expr2 as Total revenue <1000000
= sum(Total revenue<1000000)/sum(Total revenue) * 100 group by Group_Name

This one i cant do with your code

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-25 : 08:48:54

[code]SELECT SUM(CAST(A.[Total revenue] AS MONEY)
/ SUM(CAST(A.[Total revenue] AS MONEY) * 100
FROM dbo.tbl_Final_BRIT_Data AS A
INNER JOIN dbo.tbl_contextgrouping AS B ON A.SYS_ID = B.Partner_ID
WHERE (A.[Total revenue] NOT LIKE '%[^0-9]%')
GROUP BY B.Group_name
HAVING SUM(CAST(A.[Total revenue] AS MONEY) < 1000000[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources