Good day. There is a query of the following form:

SELECT COUNT(id) FROM A WHERE id_C = 2 UNION SELECT COUNT(id) A FROM A WHERE id_C = 3

Request cut as much as possible so that the essence was clear.

There are results in this form:

 4 7

It is required to add to the same result the third line the sum of the previous two lines:

 4 7 11

This is possible without filing an additional request of the form:

 SELECT COUNT(id) FROM A WHERE id_C IN(2, 3)

?

  • 3
    Without additional inquiry it is possible, but it is solved with all sorts of advanced features like rollup or СTE. Therefore, it strongly depends on the specific DBMS. rollup is for example in oracle. A suitable CTE in MS SQL and postrges. For arbitrary SQL - only the third request in the union with the query that you wrote at the end - Mike

2 answers 2

Classically, the problem is solved using WITH ROLLUP . However, in MySQL it works only within one query, in the case of UNION you have two separate queries. You can do the following:

 SELECT COUNT(id) FROM A WHERE id_C IN (2,3) GROUP BY id_C WITH ROLLUP
  • Thank you very much for the answer. Yes, it works that way. I tried to solve the problem in different ways, but as it turned out, not everything is so simple when working with UNION. - Serg

Without UNION and ROLLUP:

 select count(coalesce(a,b)) from (SELECT id_C a FROM A where id_C in(2,3) ) X full join (SELECT 4 b FROM A where id_C in(2,3) ) Y on 1=2 group by coalesce(a,b)
  • Yes, this option solves the problem, but the link with the UNION keyword was interesting. - Serg
  • It seemed to me that with UNION you know how to solve a problem. :-) - msi
  • Without the 3rd request it does not work :) - Serg
  • Then in my subqueries, you can use UNION instead of IN, if that suits you. - msi