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.
Advertisement
Answer
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