Total_2/Total_1 + Total_2 (join) help

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

Total_2/Total_1 + Total_2 (join) help

Author

Topic

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-11 : 20:43:07

I am trying to join these two on ID and then perform the following calculation.

Total_2/Total_1 + Total_2

SELECT ID, SUM (SUM_UNITS) AS TOTAL1
FROM(
SELECT ID, COUNT (UNITS) AS SUM_UNITS
FROM TABLE 
GROUP BY ID)
GROUP BY ID

SELECT ID, 
SUM(CASE WHEN STATUS = 'POL'  
		AND P_DATE IS NOT NULL 
	 THEN 1 ELSE 0 END) AS Total2   
FROM TABLE
   GROUP BY ID

Thanks for any help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-11 : 21:18:31

[code]
SELECT ID, Total1, Total2, Total2/Total_1 + Total_2
FROM
(
SELECT ID,
SUM ( COUNT(UNITS)) OVER() AS Total1,
SUM ( CASE WHEN STATUS = 'POL'
AND P_DATE IS NOT NULL
THEN 1
ELSE 0
END
) AS Total2
FROM TABLE
GROUP BY ID
) AS D
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 00:27:24

if you want decimal part make sure you do Total2*1.0/Total_1 + Total_2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-12 : 01:44:19

Thanks khtan, legend.

visakh16 I used decode ,1 for decimal.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources