The sum aggregate function is not working

[capronton]

The sum aggregate function is not working. Can someone explain why I get the error below:

[SQL]: select ev.loc_n "Location", route, sum(ev.ttp9) "ttp9"
[Error]: SQLSTATE = 42S22
Microsoft SQL Server Native Client 10.0
The multi-part identifier "ev.loc_n" could not be bound.

select ev.loc_n "Location", route, sum(ev.ttp9) "ttp9"

[SQL]: from ev, ml
where ml.loc_n=ev.loc_n and ml.id=ev.id
and ev.ts >= '2014-01-01 04:00:00.000' and ev.ts <= '2014-02-01 03:59:00.000'
and run>100 and route<799 and ev.fs in (1,2,3,4,5,6,7,8,9)
group by ev.loc_n, route
order by ev.loc_n, route

 

[bborissov]

Did you have field named LOC_N in EV table?

Borislav Borissov
VFP9 SP2, SQL Server

 

[capronton]

Yes.

 

[dhookom]

Do you have a table/view named "ev"?

What happens with:

select ev.loc_n "Location", route, sum(ev.ttp9) "ttp9"
 from ev, ml
 where ml.loc_n=ev.loc_n and ml.id=ev.id
 and ev.ts >= '2014-01-01 04:00:00.000' and ev.ts <= '2014-02-01 03:59:00.000'
 and run>100 and route<799 and ev.fs in (1,2,3,4,5,6,7,8,9)
 group by ev.loc_n, route
 order by 1,2

Duane
Hook'D on Access
MS Access MVP

 

[bborissov]

What is the result of this query (if any):

select ev.loc_n AS Location,
       route,
       sum(ev.ttp9) AS "ttp9"
from ev
INNER JOIN ml ON ml.loc_n = ev.loc_n 
             AND ml.id    = ev.id
WHERE ev.ts BETWEEN '2014-01-01 04:00:00.000' AND '2014-02-01 03:59:00.000'
  AND run   > 100 --- From what table this field is????
  AND route < 799 --- From what table this field is????
  AND ev.fs in (1,2,3,4,5,6,7,8,9)
 group by ev.loc_n, route
 order by ev.loc_n, route

If there is an error on what line is it?

Borislav Borissov
VFP9 SP2, SQL Server

 

[capronton]

I got it working and these post were very helpful.

Thanks!!