Can't refer to Column

Hello I have this query:
SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
FROM ClientCred INNER JOIN
Clients ON ClientCred.ClientID = Clients.ClientID
WHERE (Clients.Active = '-1') AND (TOTBalance > 0)
GROUP BY ClientCred.ClientID
In the where clause, I need to filter out all results that have a balance
greater than 0, so I refer to the column I created in the select clause. I
get 'Invalid Column Name TOTBalance'. Can someone please explain why this
does not work and what a solution would be? Performing the SUM in the where
clause gives an error about having an aggregate in the where.
Thanks in advance!Because server will evaluate the Where clause first.
Try this:
SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
FROM ClientCred INNER JOIN
Clients ON ClientCred.ClientID = Clients.ClientID
WHERE (Clients.Active = '-1') AND (SUM(ClientCred.Balance) > 0)
GROUP BY ClientCred.ClientID
Perayu
"Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
news:uZ1sA1JtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Hello I have this query:
> SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
> FROM ClientCred INNER JOIN
> Clients ON ClientCred.ClientID = Clients.ClientID
> WHERE (Clients.Active = '-1') AND (TOTBalance > 0)
> GROUP BY ClientCred.ClientID
> In the where clause, I need to filter out all results that have a balance
> greater than 0, so I refer to the column I created in the select clause.
> I get 'Invalid Column Name TOTBalance'. Can someone please explain why
> this does not work and what a solution would be? Performing the SUM in
> the where clause gives an error about having an aggregate in the where.
> Thanks in advance!
>
>|||WHERE clauses deal with individual rows underlying the aggragate query;
if you need to refer to the aggragate, use HAVING instead:
SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
FROM ClientCred INNER JOIN
Clients ON ClientCred.ClientID = Clients.ClientID
WHERE (Clients.Active = '-1')
GROUP BY ClientCred.ClientID
HAVING SUM(ClientCred.Balance) >0
HTH,
Stu|||Thanks Stu, this works!
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126202094.969185.94760@.g44g2000cwa.googlegroups.com...
> WHERE clauses deal with individual rows underlying the aggragate query;
> if you need to refer to the aggragate, use HAVING instead:
> SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
> FROM ClientCred INNER JOIN
> Clients ON ClientCred.ClientID = Clients.ClientID
> WHERE (Clients.Active = '-1')
> GROUP BY ClientCred.ClientID
> HAVING SUM(ClientCred.Balance) >0
> HTH,
> Stu
>|||Still doesn't work Perayu, can't have aggregate in where clause.
Thanks for the reply Stu's works.
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uc68I5JtFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Because server will evaluate the Where clause first.
> Try this:
> SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
> FROM ClientCred INNER JOIN
> Clients ON ClientCred.ClientID = Clients.ClientID
> WHERE (Clients.Active = '-1') AND (SUM(ClientCred.Balance) > 0)
> GROUP BY ClientCred.ClientID
> Perayu
> "Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
> news:uZ1sA1JtFHA.3080@.TK2MSFTNGP15.phx.gbl...
>