How do I add a column that displays the number of distinct rows to this query?
Posted
by
Fake Code Monkey Rashid
on Stack Overflow
See other posts from Stack Overflow
or by Fake Code Monkey Rashid
Published on 2010-12-29T17:30:12Z
Indexed on
2010/12/29
18:54 UTC
Read the original article
Hit count: 348
sql
|postgresql
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).
© Stack Overflow or respective owner