SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sathyarangaraj
Starting Member

India
11 Posts

Posted - 09/25/2012 :  03:35:42  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 09/25/2012 :  04:42:40  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

sathyarangaraj
Starting Member

India
11 Posts

Posted - 09/25/2012 :  08:12:51  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 09/25/2012 :  08:48:54  Show Profile  Reply with Quote
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



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000