Table Schema

\d

List of relations
Schema	Name	Type	Owner
public	alembic_version	table	akvo
public	answer	table	akvo
public	answer_id_seq	sequence	akvo
public	data	table	akvo
public	data_id_seq	sequence	akvo
public	form	table	akvo
public	form_id_seq	sequence	akvo
public	option	table	akvo
public	option_id_seq	sequence	akvo
public	question	table	akvo
public	question_group	table	akvo
public	question_group_id_seq	sequence	akvo
public	question_id_seq	sequence	akvo

Form

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'form'
ORDER  BY ordinal_position;

column_name	data_type
id	integer
name	character varying

Question Group

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'question_group'
ORDER  BY ordinal_position;

column_name	data_type
id	integer
order	integer
name	character varying
form	integer

Question

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'question'
ORDER  BY ordinal_position;

column_name	data_type
id	integer
order	integer
name	character varying
form	integer
type	USER-DEFINED
question_group	integer

Option

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'option'
ORDER  BY ordinal_position;

column_name	data_type
id	integer
order	integer
name	character varying
question	integer

Data

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'data'
ORDER  BY ordinal_position;

column_name	data_type
id	integer
form	integer
created	timestamp without time zone

SELECT COUNT(*)
FROM   data;

count
99

Answer

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'answer'
ORDER  BY ordinal_position;

column_name	data_type
id	integer
question	integer
data	integer
value	double precision
text	text
options	ARRAY

SELECT COUNT(*)
FROM   answer;

count
693

SELECT *
FROM   answer
LIMIT 16;

id	question	data	value	text	options
1	1	1			{Urban}
2	2	1			{Female}
3	3	1			{sibling}
4	4	1			{Female}
5	5	1	5
6	6	1	1
7	7	1			{“Seasonal migrant”}
8	8	1		Michael Jackson
9	1	2			{Peri-urban}
10	2	2			{Male}
11	3	2			{Offspring}
12	4	2			{Male}
13	5	2	2
14	6	2	4
15	7	2			{“Seasonal migrant”}
16	8	2		Antonio Morris

Example Query

SELECT row_number() over (partition by true) as id,form, data, 'Category 1' as name, category
FROM (
  SELECT form, data, COUNT(non_rural_female_head), 3 as valid, 'Non-Rural Female Head' as category
  FROM (SELECT form, data, CASE
    WHEN ((opt = ANY(ARRAY['Female'])) AND (question = 2)) THEN True
    WHEN ((opt = ANY(ARRAY['Head'])) AND (question = 3)) THEN True
    WHEN
    (CASE WHEN  ((opt = ANY(ARRAY['Peri-urban', 'Urban'])) AND (question = 1)) THEN True END) THEN True
    END AS non_rural_female_head
    FROM
    (SELECT
     q.form, aa.data, aa.question, unnest(aa.options)::TEXT as opt
     FROM answer aa
     LEFT JOIN question q ON q.id = aa.question) a
   ) aw
  WHERE non_rural_female_head = True
  GROUP BY data, form
  UNION
  SELECT form, data, COUNT(rural_parent_or_refugee), 2 as valid, 'Rural Parent or Refugee' as category
  FROM (SELECT form, data, CASE
    WHEN ((opt = ANY(ARRAY['Rural'])) AND (question = 1)) THEN True
    WHEN
    (CASE WHEN  ((opt = ANY(ARRAY['Parent'])) AND (question = 3)) THEN True END) OR

    (CASE WHEN  ((opt = ANY(ARRAY['Refugee/IDPs'])) AND (question = 7)) THEN True END) THEN True
    END AS rural_parent_or_refugee
    FROM
    (SELECT
     q.form, aa.data, aa.question, unnest(aa.options)::TEXT as opt
     FROM answer aa
     LEFT JOIN question q ON q.id = aa.question) a
   ) aw
  WHERE rural_parent_or_refugee = True
  GROUP BY data, form
) d WHERE d.count >= d.valid
UNION
SELECT row_number() over (partition by true) as id,form, data, 'Category 2' as name, category
FROM (
  SELECT form, data, COUNT(male_non_permanent_resident), 2 as valid, 'Male Non-Permanent Resident' as category
  FROM (SELECT form, data, CASE
    WHEN ((opt = ANY(ARRAY['Male'])) AND (question = 2)) THEN True
    WHEN ((opt = ANY(ARRAY['Nomadic'])) AND (question = 7)) THEN True
    END AS male_non_permanent_resident
    FROM
    (SELECT
     q.form, aa.data, aa.question, unnest(aa.options)::TEXT as opt
     FROM answer aa
     LEFT JOIN question q ON q.id = aa.question) a
   ) aw
  WHERE male_non_permanent_resident = True
  GROUP BY data, form
  UNION
  SELECT form, data, COUNT(female_permanent_resident), 2 as valid, 'Female Permanent Resident' as category
  FROM (SELECT form, data, CASE
    WHEN ((opt = ANY(ARRAY['Permanent'])) AND (question = 7)) THEN True
    WHEN ((opt = ANY(ARRAY['Female'])) AND (question = 4)) THEN True
    END AS female_permanent_resident
    FROM
    (SELECT
     q.form, aa.data, aa.question, unnest(aa.options)::TEXT as opt
     FROM answer aa
     LEFT JOIN question q ON q.id = aa.question) a
   ) aw
  WHERE female_permanent_resident = True
  GROUP BY data, form
) d WHERE d.count >= d.valid
ORDER BY data;

id	form	data	name	category
18	1	1	Category 1	Non-Rural Female Head
12	1	1	Category 2	Female Permanent Resident
10	1	4	Category 2	Male Non-Permanent Resident
20	1	7	Category 2	Female Permanent Resident
4	1	7	Category 1	Rural Parent or Refugee
9	1	10	Category 1	Non-Rural Female Head
11	1	15	Category 2	Female Permanent Resident
19	1	15	Category 1	Rural Parent or Refugee
1	1	16	Category 2	Female Permanent Resident
18	1	18	Category 2	Female Permanent Resident
1	1	20	Category 1	Rural Parent or Refugee
16	1	23	Category 1	Rural Parent or Refugee
17	1	27	Category 2	Male Non-Permanent Resident
22	1	28	Category 1	Non-Rural Female Head
3	1	30	Category 1	Non-Rural Female Head
14	1	33	Category 2	Female Permanent Resident
3	1	34	Category 2	Female Permanent Resident
8	1	35	Category 1	Rural Parent or Refugee
4	1	35	Category 2	Male Non-Permanent Resident
13	1	37	Category 2	Male Non-Permanent Resident
24	1	38	Category 1	Rural Parent or Refugee
15	1	40	Category 2	Female Permanent Resident
2	1	43	Category 1	Rural Parent or Refugee
21	1	45	Category 2	Female Permanent Resident
2	1	47	Category 2	Male Non-Permanent Resident
25	1	49	Category 1	Rural Parent or Refugee
28	1	50	Category 1	Rural Parent or Refugee
19	1	51	Category 2	Male Non-Permanent Resident
16	1	52	Category 2	Male Non-Permanent Resident
8	1	53	Category 2	Female Permanent Resident
5	1	54	Category 2	Male Non-Permanent Resident
12	1	56	Category 1	Rural Parent or Refugee
27	1	57	Category 1	Non-Rural Female Head
11	1	58	Category 1	Rural Parent or Refugee
21	1	59	Category 1	Rural Parent or Refugee
20	1	60	Category 1	Rural Parent or Refugee
10	1	69	Category 1	Rural Parent or Refugee
6	1	71	Category 1	Non-Rural Female Head
23	1	75	Category 1	Non-Rural Female Head
9	1	78	Category 2	Male Non-Permanent Resident
6	1	79	Category 2	Male Non-Permanent Resident
7	1	81	Category 2	Female Permanent Resident
7	1	86	Category 1	Rural Parent or Refugee
5	1	87	Category 1	Non-Rural Female Head
15	1	88	Category 1	Rural Parent or Refugee
14	1	92	Category 1	Rural Parent or Refugee
17	1	93	Category 1	Rural Parent or Refugee
22	1	94	Category 2	Male Non-Permanent Resident
13	1	96	Category 1	Non-Rural Female Head
26	1	97	Category 1	Rural Parent or Refugee

select * from (
select data, count(options) FROM (
  select *
  from answer where 'Head'=ANY(options) and question = 3
  UNION
  select *
  from answer where 'Female'=ANY(options) and question = 2
  UNION
  select *
  from answer where 'Urban'=ANY(options) or 'Peri-urban'=ANY(options) and question = 1
) d
GROUP BY data) dd
WHERE count = 3
ORDER BY data

data	count
3	3
6	3
24	3
32	3
35	3
43	3
63	3
66	3
79	3
81	3
92	3
93	3

Views

select * from ar_category

id	form	data	name	category
18	1	1	Category 1	Non-Rural Female Head
12	1	1	Category 2	Female Permanent Resident
10	1	4	Category 2	Male Non-Permanent Resident
20	1	7	Category 2	Female Permanent Resident
4	1	7	Category 1	Rural Parent or Refugee
9	1	10	Category 1	Non-Rural Female Head
11	1	15	Category 2	Female Permanent Resident
19	1	15	Category 1	Rural Parent or Refugee
1	1	16	Category 2	Female Permanent Resident
18	1	18	Category 2	Female Permanent Resident
1	1	20	Category 1	Rural Parent or Refugee
16	1	23	Category 1	Rural Parent or Refugee
17	1	27	Category 2	Male Non-Permanent Resident
22	1	28	Category 1	Non-Rural Female Head
3	1	30	Category 1	Non-Rural Female Head
14	1	33	Category 2	Female Permanent Resident
3	1	34	Category 2	Female Permanent Resident
8	1	35	Category 1	Rural Parent or Refugee
4	1	35	Category 2	Male Non-Permanent Resident
13	1	37	Category 2	Male Non-Permanent Resident
24	1	38	Category 1	Rural Parent or Refugee
15	1	40	Category 2	Female Permanent Resident
2	1	43	Category 1	Rural Parent or Refugee
21	1	45	Category 2	Female Permanent Resident
2	1	47	Category 2	Male Non-Permanent Resident
25	1	49	Category 1	Rural Parent or Refugee
28	1	50	Category 1	Rural Parent or Refugee
19	1	51	Category 2	Male Non-Permanent Resident
16	1	52	Category 2	Male Non-Permanent Resident
8	1	53	Category 2	Female Permanent Resident
5	1	54	Category 2	Male Non-Permanent Resident
12	1	56	Category 1	Rural Parent or Refugee
27	1	57	Category 1	Non-Rural Female Head
11	1	58	Category 1	Rural Parent or Refugee
21	1	59	Category 1	Rural Parent or Refugee
20	1	60	Category 1	Rural Parent or Refugee
10	1	69	Category 1	Rural Parent or Refugee
6	1	71	Category 1	Non-Rural Female Head
23	1	75	Category 1	Non-Rural Female Head
9	1	78	Category 2	Male Non-Permanent Resident
6	1	79	Category 2	Male Non-Permanent Resident
7	1	81	Category 2	Female Permanent Resident
7	1	86	Category 1	Rural Parent or Refugee
5	1	87	Category 1	Non-Rural Female Head
15	1	88	Category 1	Rural Parent or Refugee
14	1	92	Category 1	Rural Parent or Refugee
17	1	93	Category 1	Rural Parent or Refugee
22	1	94	Category 2	Male Non-Permanent Resident
13	1	96	Category 1	Non-Rural Female Head
26	1	97	Category 1	Rural Parent or Refugee

Check

select relname, relkind
from pg_class
where relkind = 'm';

relname	relkind
ar_category	m

SELECT data, unnest(options) as opt FROM answer limit 5;

data	opt
1	Urban
1	Female
1	Head
1	Female
1	Permanent

SELECT data, options as opt FROM answer limit 5;

data	opt
1	{Urban}
1	{Female}
1	{Head}
1	{Female}
1

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

database.org

database.org

Table Schema

Form

Question Group

Question

Option

Data

Answer

Example Query

Views

Check

Files

database.org

Latest commit

History

database.org

File metadata and controls

Table Schema

Form

Question Group

Question

Option

Data

Answer

Example Query

Views

Check