Hi Raffael,
This query works:
select source_address, count(distinct destination_port) as distcount from flows group by source_address having count(distinct destination_port)>1 limit 10;
The following query doesn’t work. I am just trying to use the AS keyword to make things a bit more readable.
select source_address, count(distinct destination_port) as distcount from flows group by source_address having distcount>1 limit 10; SELECT: identifier 'distcount’ unknown
The alias is not known to the group by statement, due to the order the query is evaluated in MonetDB. If you want to use the alias anyway, you can consider working with a subquery, something like this:
SELECT source_address, distcount FROM ( SELECT source_address, COUNT(DISTINCT destination_port) AS distcount GROUP BY source_address ) WHERE distcount > 1 LIMIT 10