Log Parser not working with named column in WHERE statement

Question

I am running into an issue with Log Parser where I cannot used named columns in the WHERE statement. The named column works fine in the Order By statement. Removing the named column from the WHERE statement makes the query work fine.

This is the error I get:

Error parsing query: WHERE clause: Semantic Error: WHERE clause contains aggregate functions [SQL query syntax invalid or unsupported.]

This is my query:

SELECT cs-uri-stem, count(cs-uri-stem) as hits INTO '" + $Destination + "' FROM $filePaths WHERE (cs-uri-stem LIKE '/testing%' OR cs-uri-stem LIKE '%/test%') AND (date > '2020-08-01' AND date < '2020-09-31') AND (hits > 1) Group By cs-uri-stem order by hits desc

Does Log Parser not support this?

Answer 1

No SQL dialect supports this - the results of aggregate functions cannot be referenced in the WHERE clause. You may use the HAVING clause instead for filtering aggregations:

...
Group By cs-uri-stem 
HAVING hits > 1
order by hits desc