How can I group 2 tables and count the number of rows?

To be able to use the aggregation functions COUNT, SUM, etc. you must group all the other columns that are not counted to count, add, etc.

Example:

SELECT at_ce.condicion, gpr.grupo_programa, count(*) 
   FROM at_cons_externa at_ce
        INNER JOIN gen_programa gpr ON at_ce.id_programa = gpr.id_programa
   GROUP BY at_ce.condicion, gpr.grupo_programa 

You can enter the two selections in the from:

SELECT T_CONDICION.CONDICION, T_CONDICION.CANTIDAD, T_GRUPO.grupo_programa, T_GRUPO.CANTIDAD
FROM
(select condicion, count(*) as cantidad from at_cons_externa group by condicion) AS T_CONDICION,
(select grupo_programa, count(*) as cantidad from at_cons_externa group by grupo_programa)AS T_GRUPO

The accepted answer contains what you need. Just to credit the comment I made about the "pivot table", I put here an option to generate it.

By way of example, the "row headers" will be the values of condicion and the column headings will be the values of grupo_programa (I generated 100 random values to prove that this would work well).

First, let's put the values of interest in a temporary table, to save us writing the same query over and over again

drop table if exists temp_datos;
create temporary table temp_datos
SELECT at_ce.condicion, gpr.grupo_programa
FROM at_cons_externa at_ce
     INNER JOIN gen_programa gpr ON at_ce.id_programa = gpr.id_programa;

For this solution to work, it is necessary to ensure that the GROUP_CONCAT() function can return long strings. Generally the value that I propose here is more than enough:

set session group_concat_max_len = 1024 * 1024 * 1024;

Now comes the good: Let's generate a list of expressions that add 1 for each value of the column grupo_programa present in the table, and zero otherwise. We will store these expressions in a variable ( @sql ):

set @sql = (select group_concat(distinct concat("sum(case grupo_programa when '", grupo_programa, "' then 1 else 0 end) as '", grupo_programa, "'")) from temp_datos);

We are now going to complete the query we are generating:

set @sql = concat('select condicion, ', @sql, ', count(*) as Total from temp_datos group by condicion with rollup');

If you want to see how your complete query is, you can verify it like this:

select @sql;
-- La variable SQL contiene la siguiente cadena de texto 
-- (agregué saltos de línea por claridad):
--
-- select condicion, 
-- sum(case grupo_programa when 'TARJETAS DE SALUD' then 1 else 0 end) as 'TARJETAS DE SALUD',
-- sum(case grupo_programa when 'INSTITUCIONAL/PRIVADOS' then 1 else 0 end) as 'INSTITUCIONAL/PRIVADOS', 
-- count(*) as Total 
-- from temp_datos 
-- group by condicion with rollup

Now yes, let's get the values: Prepare and execute the query that we just created:

prepare stmt from @sql;
execute stmt;

The result:

condicion             | TARJETAS DE SALUD |  INSTITUCIONAL/PRIVADOS |  Total
----------------------+-------------------+-------------------------+-------
ANULADO POR ADMISION  |                11 |                      13 |     24
CONFIRMADO            |                16 |                       6 |     22
NO PASO CONSULTA      |                 8 |                      10 |     18
OBSERVADO             |                12 |                      10 |     22
RESERVADO             |                 8 |                       6 |     14
NULL                  |                55 |                      45 |    100

That last row (with the value NULL in the column condicion contains the total of each column (this value is generated when you use GROUP BY ... WITH ROLLUP , if you do not need it, only removes WITH ROLLUP ).

When you finish, do not forget to "clean up" the prepared statement:

deallocate prepare stmt;

I hope this solution is useful to you.

Greetings