CODEWITHSUNDEEP
sqldatabasesqlitequeryingsubquery
Lilawood14
Lilawood14

Reputation: 35

SQlite nested query with counts

I've got the following table in my Sqlite database :

To my mind, the query deals with 3 cases :

Expected results are :

I'm working on the following query that doesn't work :

    SELECT req1.ID_container, req2.Number_of_line, req1.agreement_yes, req3.agreement_no
    FROM 
    (select t_ID_container,COUNT(ID_line) as agreement_yes FROM my_table WHERE agreement='Yes' GROUP BY ID_container) as req1
    INNER JOIN
    (select t_ID_container,COUNT(ID_line) as Number_of_line FROM my_table  GROUP BY ID_container) as req2 
    INNER JOIN
    (select t_ID_container,COUNT(ID_line) as agreement_no FROM my_table  WHERE agreement='No' GROUP BY ID_container) as req3
ON req1.ID_container=req2.ID_container=req3.ID_container

I'm a bit confused because I don't know how to progress.

Thanks for your help !

Upvotes: 0

Views: 544

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can do it with one query using conditional aggregation.

SELECT ID_container, 
count(*) as number_of_line,
count(case when agreement='Yes' then 1 end) as number_of_yes,
count(case when agreement='No' then 1 end) as number_of_no
FROM my_table 
GROUP BY ID_container

Upvotes: 4

Related Questions