a where problem with sum() as

[michelleqw]

Dear users,

We made a sql statement:

Example:
SELECT sum(freight) as FT
from orders
where FT > 3700
group by EmployeeID
order by FT desc

The where statement gives a problem because FT is not a field! Is there any possebillity get the right records with FT?

If yes, can someone give us source?

Nice regards,

Michelle.

 

[PHV]

SELECT sum(freight) as FT
from orders
group by EmployeeID
having sum(freight) > 3700
order by FT desc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[michelleqw]

Help,

I can't find a answer!

Michelle.

 

[PHV]

What's wrong with my suggestion ?

 

[carp]

Indeed - PHV has given you the solution. And apparently you have not tried it!

Your problem is that you are violating basic SQL syntax with a very common mistake. The problem is that
sum(freight) will not be defined until AFTER the grouping occurs. Consequently, it cannot appear in a WHERE clause. Which is precisely why the HAVING clause was born!

 

[michelleqw]

Thanks for the information.

I tried the next sql statement

SELECT sum(freight) as FT
from orders
group by EmployeeID

This is working correctly, now intoducing the 'having':

SELECT sum(freight) as FT
from orders
group by EmployeeID
having FT > 3700
order by FT desc

This gives an error!

The "solution" is the next one:

SELECT sum(freight) as FT
from orders
group by EmployeeID
having sum(freight) > 7000
order by FT desc

I find it strange that in the 'having' not the FT can be used!

Nice regards,

Michelle.

 

[PHV]

And what is the diff between your solution and mine ?

 

[Golom]

You can't use "FT" in the HAVING clause because the SELECT clause (where FT is defined) is evaluated after the HAVING clause.

Although you can sometimes get away with using a field alias like "FT" in Order By, you often need to repeat the definition of the aliased field rather that the field name in order by
i.e.
ORDER BY SUM(Freight) DESC
or
ORDER BY 1 DESC

rather than
ORDER BY FT DESC