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

PostgreSQL – return query results divided into categories

I have two tables: DOCUMENT and ITEM.
Each DOCUMENT has multiple ITEMS, so ITEM has a foreign key to DOCUMENT, let’s name it document_id.

I want to write a query, to get the total number of documents, that have specific number of items, categorized as: BELOW 10 ITEMS, BETWEEN 10 AND 20 ITMES, OVER 20 ITEMS.

So, RESULT should always return 3 rows and 2 columns – first column is as category – text, second is the total number of documents that belong to category:

document_category      | document_count
-----------------------+----------------
BELOW 10 ITEMS         | 50
BETWEEN 10 AND 20 ITEMS| 25
OVER 20 ITEMS          | 11

So far I have tried to write a subquery to return a 1-row result cosisting of 3 columns with the categorized items count:

with item_count_per_document as
(
    select
        sum(case 
                when item_count < 10 then 1
                else 0
            end) as document_count_below_10_items,
            
        sum (case 
                when item_count BETWEEN 10 and 20 then 1
                else 0
            end) as document_count_between_10_20_items,
            
        sum (case 
                when item_count > 20 then 1
                else 0
            end) as document_count_above_20_items
    from ITEM i
    left join DOCUMENT doc on doc.id = i.document_id
    WHERE doc.is_deleted = FALSE
    GROUP BY doc.id
)

but now I need to get result from this subquery, that has a form presented above – 3 rows, 2 columns.
How to achieve that?

Tags:

2

Answers


  1. 0

    if you have only three categories is UNION ALL the choice to make

    select
  MIN('BELOW 10') as category ,  sum(case 
            when item_count < 10 then 1
            else 0
        end) as document_count
      from ITEM i
left join DOCUMENT doc on doc.id = i.document_id
WHERE doc.is_deleted = FALSE
GROUP BY doc.id
  UNION ALL
     SELECT MIN('BETWEEN 10 AND 20'),   
    sum (case 
            when item_count BETWEEN 10 and 20 then 1
            else 0
        end) as document_count_between_10_20_items
      from ITEM i
left join DOCUMENT doc on doc.id = i.document_id
WHERE doc.is_deleted = FALSE
GROUP BY doc.id
  UNION ALL 
        SELECT MIN('OVER 20'),
    sum (case 
            when item_count > 20 then 1
            else 0
        end) as document_count_above_20_items
from ITEM i
left join DOCUMENT doc on doc.id = i.document_id
WHERE doc.is_deleted = FALSE
GROUP BY doc.id
    Login or Signup to reply.
  2. 0

    I would suggest to first build a list of items per document (t CTE) and a list of categories (categories CTE) and then count per category using scalar subqueries over t.

    with t(item_count) as 
( 
 select count(*) 
 from item inner join document doc on doc.id = document_id
 where not doc.is_deleted
 group by document_id
), 
categories(document_category, i) as 
(
 values ('BELOW 10 ITEMS', 1), ('BETWEEN 10 AND 20 ITEMS', 2), ('OVER 20 ITEMS', 3)
)
select document_category, 
  case when i = 1 then (select count(*) from t where item_count < 10)
   when i = 2 then (select count(*) from t where item_count between 10 and 20)
   when i = 3 then (select count(*) from t where item_count > 20)
  end document_count
from categories;

    Simpler and faster to have the result as a single row:

    with t(item_count) as 
( 
 select count(*) 
 from item join document doc on doc.id = document_id
 where not doc.is_deleted
 group by document_id
)
select count(*) filter (where item_count < 10) as 'BELOW 10 ITEMS',
       count(*) filter (where item_count between 10 and 20) as 'BETWEEN 10 AND 20 ITEMS',
       count(*) filter (where item_count > 20) as 'OVER 20 ITEMS'
from t;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search