Home / Questions / 296482
Accepted
Max Jimenez
Asked: 2020-09-27 09:15:10 +0800 CST 2020-09-27 09:15:10 +0800 CST

Count records from two tables?

  • 772

I would like to know what sentence would allow me to obtain the total number of patients (table 1) and doctors (table 2) in the same select.

In a practical case, I have 9 patients and 5 doctors. You should be able to count on the same statement, the records of the two tables and obtain a result of 14.

I have used

select count(pacnombres) from pacientes union select count(mednombres) from medicos;

But it gives me 2 rows, the first with 9 (equivalent to patients) and another with 5 (equivalent to doctors).

I have also used

select count(pacnombres and mednombres) from pacientes, medicos;

But what it is doing is multiplying 9 * 5 and it gives me 45 xD

Aid!

mysql

3 Answers

  1. Your query must formulate as follows:

    SELECT COUNT(nombre) FROM (
SELECT pacnombre AS nombre FROM pacientes
UNION ALL
SELECT mednombre AS nombre FROM medicos
) as Cantidad_Personas

    In this way, the sum of both counts should appear correctly.

    • 3
  2. Best Answer

    The simplest is to use the subqueries at the column level:

    select (select count(pacnombres) from pacientes) as pacientes,
       (select count(mednombres) from medicos)   as medicos

    combined would be:

    select (select count(pacnombres) from pacientes) +
       (select count(mednombres) from medicos)   as Total

    Clarification, this is possible, because each subquery returns a single value.

    • 2

  3. you could do it this way

    select pacnombres into #temp
from pacientes 
union all
select mednombres 
from medicos;

select count(*) from #temp

    Apparently it is not optimal but it works :)

    • 0