PostgreSQL create count, count distinct columns

Question

fairly new to PostgreSQL and trying out a few count queries. I'm looking to count and count distinct all values in a table. Pretty straightforward -

CountD  Count
351     400

With a query like this:

SELECT COUNT(*)
COUNT(id) AS count_id,
COUNT DISTINCT(id) AS count_d_id
FROM table

I see that I can create a single column this way:

SELECT COUNT(*) FROM (SELECT DISTINCT id FROM table) AS count_d_id

But the title (count_d_id) doesn't come through properly and unsure how can I add an additional column. Guidance appreciated

xehpuk · Accepted Answer · 2020-11-17 16:31:01Z

2

This is the correct syntax:

SELECT COUNT(id) AS count_id,
       COUNT(DISTINCT id) AS count_d_id
FROM table

answered Nov 17, 2020 at 16:31

xehpuk

8,3704 gold badges37 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2020-11-17 16:27:40Z

1

Your original query aliases the subquery rather than the column. You seem to want:

SELECT COUNT(*) AS count_d_id FROM (SELECT DISTINCT id FROM table) t
 -- column alias --^                           -- subquery alias --^

answered Nov 17, 2020 at 16:27

GMB

224k25 gold badges103 silver badges151 bronze badges

ok great thanks! That added in the column name properly - how can I include the additional count column?