There are a couple of sql queries of the following content (part of the query is omitted for simplicity):

select count(*) from ext_restriction er where er.proceed = '1'

and

 select count(*) from ext_restriction er where er.proceed = '0'

In principle, everything works fine, but it turned out that the ext_restriction table can have several rows with the same value in the act_id field and need to convert the query so that it works as follows: when counting the number of rows, the rows with the same act_id value should be taken into account only once, act_id , if at least in one of the lines with the same act_id the proceed = '1' flag is proceed = '1' , then they should be taken into account in the first request and should not be taken into account in the second and vice versa.

    1 answer 1

     select highproceed, count(0) from ( select act_id, max(proceed) as highproceed from ext_restriction group by act_id ) sq group by highproceed

    First we find all act_id and take the maximum proceed - i.e. if at least one line here had 1, and the rest 0, then max will return 1.

    Then we group by this value and count the quantity. We immediately consider both results, which you now consider in two queries, just to be a few lines in the answer.

    Well, for some reason, you need two queries, instead of normally counting one query, a count of 0:

     select count(0) from ( select act_id, max(proceed) as highproceed from ext_restriction group by act_id having highproceed = 0 ) sq

    To calculate separately proceed = 1 can be a bit simpler:

     select count(distinct er.act_id) from ext_restriction er where er.proceed = 1