capturing and summing string values from one column?

Question

I have this query (with some help from mr Erwin Brandstetter), which prints week intervals.

SELECT to_char(d.day, 'YYYY/MM/DD  -  ') || to_char(d.day + 6, 'YYYY/MM/DD') AS week
FROM   (
   SELECT day::date
   FROM   generate_series('2014-08-01'::date, '2014-09-14'::date, interval '1 week') day
   ) d
JOIN   account_details a ON a.date_opened >= d.day 
                        AND a.date_opened <  d.day + 6
GROUP  BY d.day;

But I want to append and sum other info depending on the value in a specific column

This should be the end result. enter image description here

So the info for activated and declined comes from one string column situation.
I tried to capture a regexp_matches match value from the column and sum the amount of captures (bellow), but it did not do the trick.

Please, how would you go about capturing and summing string values from one column?

SELECT to_char(d.day, 'YYYY/MM/DD  -  ') || to_char(d.day + 6, 'YYYY/MM/DD') AS week
     , SUM(SELECT regexp_matches(situation, 'active'))     AS activated
     , SUM(SELECT regexp_matches(situation, 'declined'))     AS declined
FROM   (
   SELECT day::date
   FROM   generate_series('2014-08-01'::date, '2014-09-14'::date, interval '1 week') day
   ) d
JOIN   account_details a ON a.date_opened >= d.day 
                        AND a.date_opened <  d.day + 6
GROUP  BY d.day;

SAMPLE DATA enter image description here So there is quite a lot more columns, but the only two will be used here. open_date and situation
situation also has quite a few different options, but only Active and Declined will be used

FIDDLE

you don't need SELECT before reexp_matches. also how are you planning to SUM result of it, if returned type is string? — Bulat
– Bulat, Commented Sep 16, 2014 at 7:47
I want to SUM the number of occurrences or matches. No idea, this is my second day using Postgres so Im trying to learn, but the docs is not all THAT clear. — morne
– morne, Commented Sep 16, 2014 at 7:50

Bulat · Accepted Answer · 2014-09-16 09:50:20Z

1

Not sure if this will work, but possibly you want to count matches:

SELECT to_char(d.day, 'YYYY/MM/DD  -  ') || to_char(d.day + 6, 'YYYY/MM/DD') AS week
     , SUM(CASE WHEN LOWER(situation) LIKE '%active%' THEN 1 ELSE 0 END)     AS activated
     , SUM(CASE WHEN LOWER(situation) LIKE '%declined%' THEN 1 ELSE 0 END) AS declined
FROM   (
   SELECT day::date
   FROM   generate_series('2014-08-01'::date, '2014-09-14'::date, interval '1 week') day
   ) d
JOIN   account_details a ON a.date_opened >= d.day 
                        AND a.date_opened <  d.day + 6
GROUP  BY d.day;

edited Sep 16, 2014 at 9:50

answered Sep 16, 2014 at 7:56

Bulat

6,9992 gold badges32 silver badges54 bronze badges

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

4 Comments

morne Over a year ago

Thanks Bulat, but its just 0s all the way.

Bulat Over a year ago

Can you add sample data to your question or set up sqlfiddle.com?

morne Over a year ago

I have put down some demo data. I will try to make a fiddle

morne Over a year ago

ah, okay, I see the case sensitivity.. Thanks you Bulat. Im gonna try to do the total and percentages, but if I dont get it right, will you be able to assist me later on please.

Collectives™ on Stack Overflow

capturing and summing string values from one column?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related