CODEWITHSUNDEEP
sqlpostgresqlcountsubquerydistinct
paranormaldist
paranormaldist

Reputation: 508

PostgreSQL create count, count distinct columns

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

Upvotes: 0

Views: 1176

Answers (2)

xehpuk
xehpuk

Reputation: 8241

This is the correct syntax:

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

Upvotes: 1

GMB
GMB

Reputation: 222482

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 --^

Upvotes: 1

Related Questions