PostgreSQL - for each loop

Question

I need something like this

SELECT
foreach(service IN (SELECT tbl_services.service_name FROM tbl_services))
    COUNT(CASE WHEN service=ANY(tbl_providers.provided_services) THEN 1 ELSE NULL END)
FROM tbl_providers;

Is there any specific solution for implementing this type of for each in sql? thanks

When you start thinking in "loops" in SQL you are doing something wrong. — user330315
– user330315, Commented Jul 11, 2018 at 10:41
Please edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. (edit your question - do not post code or additional information in comments) — user330315
– user330315, Commented Jul 11, 2018 at 10:49

Kai · Accepted Answer · 2018-07-11 10:47:16Z

1

Following query gives you same result (number of services provided by any provider):

SELECT count(distinct s.service)
FROM tbl_services s
INNER JOIN tbl_providers p ON p.provided_services @> ARRAY[s.service]::varchar[]

edited Jul 11, 2018 at 10:47

answered Jul 11, 2018 at 10:34

Kai

3371 silver badge4 bronze badges

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

3 Comments

pouria daneshvar Over a year ago

thank you kind sir for your answer. p.provided_services is an array, including many services each provider able to provide. So it's not possible to join them.

404 Over a year ago

@pouriadaneshvar would something like INNER JOIN tbl_providers p ON s.service = ANY(p.provided_services) work?

Kai Over a year ago

@pouriadaneshvar please, see updated query above. Made if provided_services array contains service.

Collectives™ on Stack Overflow

PostgreSQL - for each loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related