Can't refer to Column

8 Sep 2005 5:46 PM

Amon Borland

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!

8 Sep 2005 5:53 PM

Perayu

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

Show quote

"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!
>
>
>

8 Sep 2005 6:01 PM

Amon Borland

Still doesn't work Perayu, can't have aggregate in where clause.

Thanks for the reply Stu's works.

Show quote

"Perayu" <yu***@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...
>> 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!
>>
>>
>>
>
>

8 Sep 2005 5:54 PM

Stu

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

8 Sep 2005 6:01 PM

Amon Borland

Thanks Stu, this works!

Show quote

"Stu" <stuart.ainswo***@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
>