how to count particular values from an array in postgresql

Question

I have a table called "User" where it has the details or user_id and product_item_code.

ex:

Select * from Users limit 5;
+----+---------+---------------------------+
| id | user_id |     product_item_code     |
+----+---------+---------------------------+
|  1 |     123 | {556,772,945}             |
|  2 |     124 | {556,965,945,990}         |
|  3 |     125 | {772, 435, 990, 556}      |
|  4 |     126 | {556, 623, 842}           |
|  5 |     127 | {842, 990, 556, 623, 745} |
+----+---------+---------------------------+

I want to count these product_item_code 556, 990, 623. How many times it's been repeated.

I am looking for a query to give me an output like below

+-------------------+-------+
| product_item_code | count |
+-------------------+-------+
|               556 |     5 |
|               990 |     3 |
|               623 |     2 |
+-------------------+-------+

I have tried the below code but couldn't get the expected output.

select count(1) from Users where ARRAY[556, 990, 623] @> ANY(product_item_code);

Please let me know how can I get the above output. Thanks in advance

Unrelated to your question, but count(1) is actually slower than count(*) — user330315
– user330315, Commented Jun 29, 2020 at 18:27

Oto Shavadze · Accepted Answer · 2020-06-29 17:19:45Z

2

You can use unnest array values and then count them, like :

select u, count(*) from users
join lateral unnest(product_item_code) u on true
where
u in(556, 990, 623)
group by u
order by count(*) desc

answered Jun 29, 2020 at 17:19

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

Comments

GMB · Accepted Answer · 2020-06-29 21:12:27Z

1

No need to unnest. Assuming that a given item never appears twice in a given array, you can enumerate the values in a derived table, join with any(), and aggregate:

select p.code, count(*) cnt
from (values (556), (990), (223)) p(code)
inner join users u on p.code = any(u.product_item_code)
group by p.code

answered Jun 29, 2020 at 21:12

GMB

224k25 gold badges103 silver badges151 bronze badges

Comments

Mike Organek · Accepted Answer · 2020-06-29 17:23:51Z

0

Use unnest to turn the arrays into rows and then group by product_item_code to get your counts:

=# with expand as (
  select unnest(product_item_code) as product_item_code
    from users
)
select product_item_code, count(*)
  from expand
 where product_item_code in (556, 990, 623)
 group by product_item_code
 order by count(*) desc;

 product_item_code | count
-------------------+-------
               556 |     5
               990 |     3
               623 |     2
(3 rows)

answered Jun 29, 2020 at 17:23

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Collectives™ on Stack Overflow

how to count particular values from an array in postgresql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related