Query using Avg Ignoring Certain Values

In this case we want to get the average of values but we also want to ignore certain values, say if when the deviceType column is greater than 0 and not a negative number. The mysql engine will ignore null values so we can accomplish this using a case statement or MySQLs build in if function

select
	sum(clicks) as  clicks,
	AVG(IF(deviceType > 0
        deviceType,
        NULL)) as 'AvgDevicesGreaterThanZero',
	term
from clickData
group by term

Leave a Reply

Your email address will not be published.