How do I add a column that displays the number of distinct rows to this query?
- by Fake Code Monkey Rashid
Hello good people!
I don't know how to ask my question clearly so I'll just show you the money.
To start with, here's a sample table:
CREATE TABLE sandbox (
id integer NOT NULL,
callsign text NOT NULL,
this text NOT NULL,
that text NOT NULL,
"timestamp" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE SEQUENCE sandbox_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE sandbox_id_seq OWNED BY sandbox.id;
SELECT pg_catalog.setval('sandbox_id_seq', 14, true);
ALTER TABLE sandbox ALTER COLUMN id SET DEFAULT nextval('sandbox_id_seq'::regclass);
INSERT INTO sandbox VALUES (1, 'alpha', 'foo', 'qux', '2010-12-29 16:51:09.897579+00');
INSERT INTO sandbox VALUES (2, 'alpha', 'foo', 'qux', '2010-12-29 16:51:36.108867+00');
INSERT INTO sandbox VALUES (3, 'bravo', 'bar', 'quxx', '2010-12-29 16:52:36.370507+00');
INSERT INTO sandbox VALUES (4, 'bravo', 'foo', 'quxx', '2010-12-29 16:52:47.584663+00');
INSERT INTO sandbox VALUES (5, 'charlie', 'foo', 'corge', '2010-12-29 16:53:00.742356+00');
INSERT INTO sandbox VALUES (6, 'delta', 'foo', 'qux', '2010-12-29 16:53:10.884721+00');
INSERT INTO sandbox VALUES (7, 'alpha', 'foo', 'corge', '2010-12-29 16:53:21.242904+00');
INSERT INTO sandbox VALUES (8, 'alpha', 'bar', 'corge', '2010-12-29 16:54:33.318907+00');
INSERT INTO sandbox VALUES (9, 'alpha', 'baz', 'quxx', '2010-12-29 16:54:38.727095+00');
INSERT INTO sandbox VALUES (10, 'alpha', 'bar', 'qux', '2010-12-29 16:54:46.237294+00');
INSERT INTO sandbox VALUES (11, 'alpha', 'baz', 'qux', '2010-12-29 16:54:53.891606+00');
INSERT INTO sandbox VALUES (12, 'alpha', 'baz', 'corge', '2010-12-29 16:55:39.596076+00');
INSERT INTO sandbox VALUES (13, 'alpha', 'baz', 'corge', '2010-12-29 16:55:44.834019+00');
INSERT INTO sandbox VALUES (14, 'alpha', 'foo', 'qux', '2010-12-29 16:55:52.848792+00');
ALTER TABLE ONLY sandbox
ADD CONSTRAINT sandbox_pkey PRIMARY KEY (id);
Here's the current SQL query I have:
SELECT
*
FROM
(
SELECT
DISTINCT ON (this, that)
id, this, that, timestamp
FROM
sandbox
WHERE
callsign = 'alpha'
AND
CAST(timestamp AS date) = '2010-12-29'
)
playground
ORDER BY
timestamp
DESC
This is the result it gives me:
id this that timestamp
-----------------------------------------------------
14 foo qux 2010-12-29 16:55:52.848792+00
13 baz corge 2010-12-29 16:55:44.834019+00
11 baz qux 2010-12-29 16:54:53.891606+00
10 bar qux 2010-12-29 16:54:46.237294+00
9 baz quxx 2010-12-29 16:54:38.727095+00
8 bar corge 2010-12-29 16:54:33.318907+00
7 foo corge 2010-12-29 16:53:21.242904+00
This is what I want to see:
id this that timestamp count
-------------------------------------------------------------
14 foo qux 2010-12-29 16:55:52.848792+00 3
13 baz corge 2010-12-29 16:55:44.834019+00 2
11 baz qux 2010-12-29 16:54:53.891606+00 1
10 bar qux 2010-12-29 16:54:46.237294+00 1
9 baz quxx 2010-12-29 16:54:38.727095+00 1
8 bar corge 2010-12-29 16:54:33.318907+00 1
7 foo corge 2010-12-29 16:53:21.242904+00 1
EDIT:
I'm using PostgreSQL 9.0.* (if that helps any).
