Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with Query 1

Status
Not open for further replies.

alexanderthegreat

IS-IT--Management
Sep 9, 2005
70
US
SELECT
( SELECT SUM(aes.S2C_TTL) FROM dbo.AES_ES_2 AS aes )
+ ( SELECT SUM(aes.S2C_TTL) FROM dbo.AES_HS_2 AS aes )
AS TotalArtsSpending / (Select ENROLL_TT from AES_CONTACT_SCHOOL)AS PerPupilSpending

Im trying to take the sum of two fields and divide by another while assigning a new name to each calculaton
 
Code:
SELECT 

    ( SELECT SUM(S2C_TTL) FROM dbo.AES_ES_2) 
  + ( SELECT SUM(S2C_TTL) FROM dbo.AES_HS_2) AS TotalArtsSpending,

    (( SELECT SUM(S2C_TTL) FROM dbo.AES_ES_2) 
  +  ( SELECT SUM(S2C_TTL) FROM dbo.AES_HS_2))
  / (Select ENROLL_TT from AES_CONTACT_SCHOOL)AS PerPupilSpending


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]
 
SELECT

( SELECT SUM(S2C_TTL) FROM dbo.AES_ES_2)
+ ( SELECT SUM(S2C_TTL) FROM dbo.AES_HS_2) AS TotalArtsSpending,

(( SELECT SUM(S2C_TTL) FROM dbo.AES_ES_2)
+ ( SELECT SUM(S2C_TTL) FROM dbo.AES_HS_2))
/ (Select ENROLL_TTL from AES_CONTACT_SCHOOL)AS PerPupilSpending


Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.

We need the sum of ENROLL_TTL
 
This
Code:
Select ENROLL_TTL from AES_CONTACT_SCHOOL
is the problem

Should it be
Code:
Select [COLOR=red]SUM([/color]ENROLL_TTL[COLOR=red])[/color] from AES_CONTACT_SCHOOL
?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top