I have a table like this:
CREATE TABLE tbl ( host text NOT NULL, adr ip4 NOT NULL, usr text NOT NULL );
(ip4 is from the ip4r contrib module)
and I want the number of entries per address and per user:
SELECT adr, usr, count(*) FROM tbl WHERE host = ? AND adr <<= ? GROUP BY adr, usr ORDER BY adr, usr
That's pretty basic stuff and returns something like this:
adr1 usr1_1 cnt1_1 adr1 usr1_2 cnt1_2 adr1 usr1_3 cnt1_3 adr2 usr2_1 cnt2_1 ...
But I want the address to be NULL if it's the same as the address of
the previous row. I came up with this:
SELECT CASE lag(adr) OVER (ORDER BY adr) WHEN adr THEN NULL ELSE adr END AS myaddr, usr,
count(*)FROM tbl WHERE host = ? AND adr <<= ? GROUP BY adr, usr ORDER BY adr, usr
This returns something like
adr1 usr1_1 cnt1_1 NULL usr1_2 cnt1_2 NULL usr1_3 cnt1_3 adr2 usr2_1 cnt2_1 ...
what's exactly what I want. But when I don't name the CASE expression
(i.e. I delete "AS myaddr"), I get the following:
adr1 usr1_1 cnt1_1 adr2 usr2_1 cnt2_1 ...
The other users for one address are gone. Does anyone know why?