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?
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
