Algeria '{"validated": "correct"}' 5
Algeria '{"validated": "incorrect"}' 1My data looks like this: 
USA '{"validated": "correct"}' 486
USA '{"validated": "incorrect"}' 21

and I am trying to cross tab it like this:

Country Correct Incorrect
Algeria 5       1 
USA     486     21

Here is my SQL query,

select * from crosstab
(
select country,validate_obj,count(validate_obj) as row_count
from (
select adg.article_id, to_timestamp(adg.ts_start),adv.validate_obj, regexp_replace(location_name, '.*,', '') as country
from table1 adg 
inner join table2 ade on adg.article_id = ade.article_id 
inner join table3 adv on adg.article_id = adv.article_id 
where adv.ts_end !=0
) as rollup_table
group by country, validate_obj
order by 1,2
, $$VALUES ('{"validated": "correct"}'::text), ('{"validated": "incorrect"}')$$
) AS ct ("country" text, "Correct" int, "Incorrect" int)

I am not able to do it since I do not have permissions to create extensions (tablefunc)

Is there an alternate way?

Can you please help? Thank you.

CodePudding user response：

As you ave only 2 values, you can make the classical approach to pivot

CREATE TABLE mytable (
  "country" VARCHAR(7),
  "value" JSON,
  "count_" INTEGER
);

INSERT INTO mytable
  ("country", "value", "count_")
VALUES
  ('Algeria', '{"validated": "correct"}', '5'),
  ('Algeria', '{"validated": "incorrect"}', '1'),
  ('USA', '{"validated": "correct"}', '486'),
  ('USA', '{"validated": "incorrect"}', '21');

SELECT 
"country",
MAX(CASE WHEN "value" ->> 'validated'  = 'correct' THEN count_ ELSe NULL END) AS  correct
,
MAX(CASE WHEN "value" ->> 'validated'  = 'incorrect' THEN count_ ELSe NULL END) AS  incorrect
FROM
    mytable
GROUP BY "country"    

country | correct | incorrect
:------ | ------: | --------:
Algeria |       5 |         1
USA     |     486 |        21

a_horse_with_no_name pointed out, that there is also an alternative

SELECT 
"country",
max(count_) filter (where "value" ->> 'validated' = 'correct') AS  correct
,
max(count_) filter (where "value" ->> 'validated' = 'incorrect') AS  incorrect
FROM
    mytable
GROUP BY "country"    

country | correct | incorrect
:------ | ------: | --------:
Algeria |       5 |         1
USA     |     486 |        21

db<>fiddle here