Local OSM server query

Question

I need to make a query for my local OSM server which returns a table with 2 columns: user.display_name and the total of nodes of the ways with tag k='waterway' created, edited or deleted by that user.

I think it should be something like:

SELECT
    users.display_name,
    COUNT(current_way_nodes)
FROM
    users
INNER JOIN changesets ON users.id = changesets.user_id
INNER JOIN current_ways ON changesets.id = current_ways.changeset_id
INNER JOIN current_way_tags ON current_ways.id = current_way_tags.way_id
INNER JOIN current_way_nodes ON current_ways.id = current_way_nodes.way_id
WHERE users.display_name = 'foo' AND current_way_tags.k = 'waterway';

But this query gives me the following error:

ERROR:  column "users.display_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:     users.display_name,

If someone need to have a look at the schema, it's available here: https://wiki.openstreetmap.org/w/images/5/58/OSM_DB_Schema_2016-12-13.svg

Answer 1

The following code did the job:

SELECT
    users.display_name,
    COUNT(way_nodes)
FROM
    users
INNER JOIN changesets ON users.id = changesets.user_id
INNER JOIN ways ON changesets.id = ways.changeset_id
INNER JOIN way_tags ON ways.way_id = way_tags.way_id
INNER JOIN way_nodes ON ways.way_id = way_nodes.way_id
WHERE way_tags.k = 'waterway'
GROUP BY
    display_name;