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

Filed under:
|

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

Related posts about sql

Related posts about postgresql