WITH s1 as (SELECT pos_id as pi1
FROM pos_type p1
WHERE description = 'N'),
s2 as (SELECT p2.pos_id as term
FROM pos_type p2, s1
WHERE p2.parent_pos = s1.pi1),
v1 as( SELECT pos_id as piv1
FROM pos_type pv1
WHERE description = 'V'),
v2 as( SELECT pv2.pos_id as verb
FROM pos_type pv2, v1
WHERE pv2.parent_pos = v1.piv1),
v3 as (SELECT COUNT(dt2.term) as anzahl_verben
FROM document d2, document_term dt2, pos_type p4
WHERE d2.document_id = dt2.document_id
AND dt2.pos_id = p4.pos_id
AND EXISTS (SELECT *
FROM v2
WHERE p4.pos_id = v2.verb)
group by d2.document_id, d2.title),
s3 as (SELECT d.document_id, d.title, count(dt.term) as anzahl_substantive
FROM document d, document_term dt, pos_type p3
WHERE d.document_id = dt.document_id
AND dt.pos_id = p3.pos_id
AND EXISTS (SELECT *
FROM s2
WHERE p3.pos_id = s2.term)
group by d.document_id, d.title)
SELECT d3.document_id, d3.title, s3.anzahl_substantive, v3.anzahl_verben
FROM document d3, document_term dt3, pos_type p3, s3, v3
WHERE v3.anzahl_verben < s3.anzahl_substantive / overcomplicated and not legal, am in too deep /
order by d3.document_id
WITH s1 as (SELECT pos_id as pi1 FROM pos_type p1 WHERE description = 'N'),
s2 as (SELECT p2.pos_id as term FROM pos_type p2, s1 WHERE p2.parent_pos = s1.pi1),
v1 as( SELECT pos_id as piv1 FROM pos_type pv1 WHERE description = 'V'),
v2 as( SELECT pv2.pos_id as verb FROM pos_type pv2, v1 WHERE pv2.parent_pos = v1.piv1),
v3 as (SELECT COUNT(dt2.term) as anzahl_verben FROM document d2, document_term dt2, pos_type p4 WHERE d2.document_id = dt2.document_id AND dt2.pos_id = p4.pos_id AND EXISTS (SELECT * FROM v2 WHERE p4.pos_id = v2.verb) group by d2.document_id, d2.title),
s3 as (SELECT d.document_id, d.title, count(dt.term) as anzahl_substantive FROM document d, document_term dt, pos_type p3 WHERE d.document_id = dt.document_id AND dt.pos_id = p3.pos_id AND EXISTS (SELECT * FROM s2 WHERE p3.pos_id = s2.term) group by d.document_id, d.title)
SELECT d3.document_id, d3.title, s3.anzahl_substantive, v3.anzahl_verben FROM document d3, document_term dt3, pos_type p3, s3, v3 WHERE v3.anzahl_verben < s3.anzahl_substantive / overcomplicated and not legal, am in too deep / order by d3.document_id