Delete rows based on an avg value of a field

[craigorama]

In my table I have 2 columns with x,y points and I need to remove any rows where there are 'wacky points' (basically points that are obviously not in synch with the rest of the points in the table).

I'm trying to construct an SQL query that calculates an average for each column and then removes rows where the values are outside a set tolerance value.

I tried to do something like the statement below but got an error saying I cannot have an agregate function in the where clause:

SELECT *
FROM TELLUS_TEMP_UPLOAD
WHERE COLLAR_LAT < (Avg(COLLAR_LAT)-50)
OR COLLAR_LAT > (Avg(COLLAR_LAT)+50)
OR COLLAR_LNG < (Avg(COLLAR_LNG)-50)
OR COLLAR_LNG<(Avg(COLLAR_LNG)+50);


How would i go about selecting, then deleting the rows that fall with the values stated above?

Thanks!

 

[PHV]

A starting point:

SELECT * 
FROM TELLUS_TEMP_UPLOAD
WHERE Abs(COLLAR_LAT - DAvg('COLLAR_LAT','TELLUS_TEMP_UPLOAD')) > 50
   OR Abs(COLLAR_LNG - DAvg('COLLAR_LNG','TELLUS_TEMP_UPLOAD')) > 50

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

The reason it wouldn't work is because you need to use the HAVING clause to filter on an aggregate function. Say you wanted to see total orders by customer:

SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customer

this will return:
[tt]
1 10000
2 20000[/tt]

if you only want customers who live in California, you would put that in a WHERE clause:

SELECT Customer, SUM(OrderTOtal) From Orders WHERE State = "CA" Group by Customer

If you only want customers who have a OrderTotal sum of greater than 15000 then you would use a HAVING clause:

SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customers HAVING Sum(OrderTOtal) > 15000

To get customers in California with an OrderTotal > 15000 then:

SELECT Customer, SUM(OrderTotal) FROM Orders WHERE State = "CA" GROUP BY Customer HAVING Sum(OrderTotal) > 15000

While PHV has provided a solution that doesn't require the HAVING clause, it's a valuable filter.

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[craigorama]

Thanks for your help,

I looked into having originally but hit a couple of beginner road blocks.

I ended up using a sub query to get what i wanted:

DELETE *
FROM TELLUS_TEMP_UPLOAD
WHERE COLLAR_LAT<(SELECT Avg(COLLAR_LAT) FROM TELLUS_TEMP_UPLOAD)-50
Or COLLAR_LAT>(SELECT Avg(COLLAR_LAT) FROM TELLUS_TEMP_UPLOAD)+50
or COLLAR_LNG<(SELECT Avg(COLLAR_LNG) FROM TELLUS_TEMP_UPLOAD) -50
Or COLLAR_LNG>(SELECT Avg(COLLAR_LNG) FROM TELLUS_TEMP_UPLOAD)+50;

I appreciate the pokes in the right direction!