Question 173975 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to combine two counts on one table?

The table is like:

country fruit size
Japan Apple Big
Japan Apple Small
Japan Orange Big
Singapore Banana Small

The expect output is like:

country type total
Japan 2 3
Singapore 1 1

I managed to run the counts separately.
Fruit type:

SELECT country, COUNT(DISTINCT fruit) AS type 
FROM Table 
GROUP BY country

Fruit total:

SELECT COUNT(fruit) 
FROM Table 
GROUP BY country
Tags:

2

Answers


  1. 0

    You can combine your queries into one :

    SELECT country, COUNT(DISTINCT fruit) AS type, COUNT(fruit) as total
FROM Table
GROUP BY country;
    Login or Signup to reply.
  2. 0

    Like has been commented, you can simply put both aggregates in the same SELECT list. count(*) is faster. One difference: it counts all rows, while count(fruit) only counts non-null values. If fruit is NOT NULL, both do the same.

    SELECT country, count(DISTINCT fruit) AS type, count(*) AS total
FROM   tbl
GROUP  BY country;

    count(DISTINCT ...) is comparatively expensive. Depending on Postgres version and data distribution, this may be faster (doing the same):

    SELECT country, count(*) AS type, sum(fruit_ct) AS total
FROM  (
   SELECT country, fruit, count(*) AS fruit_ct
   FROM   tbl
   GROUP  BY 1, 2
   ) sub
GROUP  BY 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search