I want to SUM a column in an SQL query but I'm getting an error; Invalid Column Name.?

Question

I want to total the TotalUsage column. I'm very new to SQL. Can any one tell me what I'm doing wrong? This is the error:
Server: Msg 207, Level 16, State 3, Line 12
Invalid column name 'TotalUsage'.

This is the query:

SELECT r.endPointId, SUM(r.usage) as TotalUsage, s.revenueClass, m.multiplier
FROM serviceLocations s, Meters m, Readings r, EndPoints e
WHERE r.endPointId = e.endPointID and e.meterID = m.meterID
and s.serviceLocationId=m.serviceLocationId and s.revenueClass Like '__1'
and (readingDate > '09/29/2007') AND (readingDate < '10/31/2007')
and (quality = 0) and billingCycleId = '3'

GROUP BY r.endPointId, s.revenueClass, m.multiplier
Having sum(r.usage) > 0
Order by s.revenueClass

select SUM(TotalUsage);

Answer 1

You are trying to store the value in parameter but you have not used one in your last select clause. However, this won't really work either. What you need to do is move your last select statement above the rest of your query and use that query as a sub query like this.



select SUM(TotalUsage)

from

(

SELECTr.endPointId, SUM(r.usage) as TotalUsage, s.revenueClass, m.multiplier

FROM serviceLocations s, Meters m, Readings r, EndPoints e

WHERE r.endPointId = e.endPointID and e.meterID = m.meterID

and s.serviceLocationId=m.serviceL... and s.revenueClass Like '__1'

and (readingDate > '09/29/2007') AND (readingDate < '10/31/2007')

and (quality = 0) and billingCycleId = '3'



GROUP BY r.endPointId, s.revenueClass, m.multiplier

Having sum(r.usage) > 0

) x

Answer 2

It's been a couple years. From the error, you either mispelled a column.



From your code, I think the error is coming from (readingDate > '09/29/2007') AND (readingDate < '10/31/2007')

and (quality = 0) and billingCycleId = '3'



What table is readingDate and billingCycleid and Quality in? Could their be multiple columns with that name?

Answer 3

Hmmm.... First off, I think it may help if you explicitly specify the 'readingDate' and 'quality' columns by adding the 'e.' or 's.' or which ever table they are coming from to the front of them. So, is the 'SELECT SUM(TotalUsage)' being executed in a different, distinct query in SQL*Plus? If it is, you can't reference back to 'TotalUsage', as the moment after you execute the first query, you lose the handle to it. Maybe you need to do something like



SELECT SUM(TotalUsage) FROM

(SELECT r.endPointId, SUM(r.usage), as TotalUsage, s.rev...

)



In other words, include the entire big query as a subquery within the 'SUM' one. You may to do a little tweaking, but I think that will get you what you want. Good luck : )