Como contar la cantidad de id de tabla1 y tabla 2 sin problema

Question

Quiero hacer una consulta donde me cuente la cantidad de id de tabla1 y tabla2 sin que en el where afecte una a la otra (digo esto porque ambas tablas comparten un campo llamado ESTADO  que es "LISTO" o "PENDIENTE" en tabla1 hay una cantidad de 10 id y en tabla2 hay 4 lo que quiero es lo siguiente que en tabla 1 tome los 10 id contados y en la tabla 2 tomar solo los que tienen el ESTADO en LISTO ya he hecho la consulta pero no me toma los datos como los quiero solo me toma los 2 FINALIZADOS  de la tabla 2 en ambos resultado 

SELECT COUNT( a.Id_sol ) AS Ba, COUNT( b.Id_orden ) AS Ma
FROM solicitud AS a
INNER JOIN orden b ON a.Id_sol = b.Id_sol
WHERE b.Estado = 'Finalizado'

el resultado que quiero que me salga seria

Ba = 10  Ma = 2 

Answer 1

Hola Antunez91:

Te expongo dos posibles soluciones, de entre todas las variantes que puedan existir:

CREATE TABLE solicitud
(id_sol INT, 
 estado VARCHAR(100)
);
CREATE TABLE orden
(id_sol   INT, 
 id_orden INT, 
 estado   VARCHAR(100)
);
go
insert into solicitud 
(id_sol, estado )
values 
(1,'listo'),
(2,'listo'),
(3,'listo'),
(4,'listo'),
(5,'listo'),
(6,'listo'),
(7,'listo'),
(8,'listo'),
(9,'listo'),
(10,'finalizado');
go
insert into orden 
(id_sol, id_orden, estado )
values 
(1,1,'finalizado'),
(2,1,'pendiente'),
(4,1,'pendiente'),
(4,2,'finalizado');
go
/* fin de creación del escenario */

/* opcion 1 utilizando cross apply */
SELECT COUNT(a.id_sol) AS ba, 
       b.ma
FROM solicitud AS a
     CROSS APPLY
(
    SELECT COUNT(b.id_orden) AS ma
    FROM orden b
    WHERE b.estado = 'finalizado'
) AS b
GROUP BY b.ma;

/* opcion 2 utilizando una subconsulta */

SELECT COUNT(a.id_sol) AS ba, 
(
    SELECT COUNT(b.id_orden) AS ma
    FROM orden AS b
    WHERE b.estado = 'finalizado'
) AS ma
FROM solicitud AS a;

Answer 2

Amigo funciona a la perfeccion muchas gracias,  una ultima duda mas si quisiera ver cuantos id hay en cada mes siempre utilizando esta misma consulta 

SELECT MONTHNAME( a.Fecha ) AS Mes, COUNT( a.id_sol ) AS ba, (

SELECT COUNT( b.id_orden ) AS ma
FROM orden AS b
WHERE b.estado = 'Finalizado'

) AS ma
FROM solicitud AS a
GROUP BY YEAR( a.Fecha ) , MONTH( a.Fecha ) 

me muestra correctamente los datos de ba pero cuando quiero aplicarlo al de ma me sale error nuevamente mil gracias 

Answer 3

De nada

Answer 4

Se podra hacer eso en esa consulta?

Answer 5

Hola Antunez91:

Pero, ¿cual es la estructura de las dos tablas?

Si cambias el requerimiento, no vale con aportar una nueva consulta, tendrás que aportar la información adicional.

Answer 6

es esta misma

SELECT MONTHNAME( a.Fecha ) AS Mes, COUNT( a.id_sol ) AS ba, (

SELECT COUNT( b.id_orden ) AS ma
FROM orden AS b
WHERE b.estado = 'Finalizado'

) AS ma
FROM solicitud AS a
GROUP BY YEAR( a.Fecha ) , MONTH( a.Fecha ) 

solo que quisiera que me imprimera de esta manera

Mes     ba   ma
January  4   2
February 2   0
April    2   0
June     1   0
December 1   0

No se si es posible el problema que tengo es que los datos de ba los muestra bien pero en ma en todos los meses repite 2 y solo quiero que me cuente los datos que hay en el mes que se guardo

Answer 7

Hola Antunez91:

Me extraña un poco ese "MONTHNAME".

No obstante, te doy una solución en SQL Server:

WITH cte
     AS (SELECT DATENAME(MONTH, (a.fecha)) AS mes, 
                COUNT((a.id_sol)) AS solicitudes,
                CASE
                    WHEN b.id_sol IS NULL
                    THEN 0
                    ELSE 1
                END AS ordenesCerradas
         FROM solicitud a
              LEFT JOIN orden b ON a.id_sol = b.id_sol
         WHERE b.estado = 'finalizado'
               OR b.estado IS NULL
         GROUP BY a.fecha, 
                  b.id_sol)
     SELECT c.mes AS Mes, 
            COUNT(c.solicitudes) AS ba, 
            SUM(ordenesCerradas) AS ma
     FROM cte c
     GROUP BY c.mes;

Para que te cuente ma como parte de ba, es claro que tienen que estar relacionados, y hasta ahora no lo estaban.

Para comprender el howTo de la consulta, simplemente, ejecuta lo que hay dentro del cte (Select... b.id_sol) y con eso ves lo que construye la consulta utilizando un left join, y en la salida del cte, simplemente se realiza la cuenta de ellos.

Si tienes dudas lo comentas.

Salida

Count

https://docs.microsoft.com/es-es/sql/t-sql/functions/count-transact-sql?view=sql-server-2017

Tablas de expresión común

https://javifer2.blogspot.com/2019/01/with-cte-tablas-de-expresion-comun-1_1.html

Left Join

https://docs.microsoft.com/es-es/sql/relational-databases/performance/joins?view=sql-server-2017

Answer 8

Creo entender el proceso amigo, estoy usando el wamp server con mysql al ejecutar la consulta me tira este error **MySQL server version for the right syntax to use near 'cte as ( select monthname(a.Fecha) as Mes, count(Id_sol) as Solicitudes, case ' at line 1**

y ya investigue y es lo mismo en mysql pero no e cual sea la razon

Answer 9

Es importante siempre saber el motor que va a ejecutar lo que pides, porque aunque se parecen, cada uno opera a su manera. Cuando vi el MonthName, ya tenía claro que era mysql, pero como no habías comentado nada, podía ser que tu vinieses de ese motor, y no supieses que en tsql no es así.

SELECT c.mes AS Mes, 
            COUNT(c.solicitudes) AS ba, 
            SUM(ordenesCerradas) AS ma
			from 
      (SELECT DATENAME(MONTH, (a.fecha)) AS mes, 
                COUNT((a.id_sol)) AS solicitudes,
                CASE
                    WHEN b.id_sol IS NULL
                    THEN 0
                    ELSE 1
                END AS ordenesCerradas
         FROM solicitud a
              LEFT JOIN orden b ON a.id_sol = b.id_sol
         WHERE b.estado = 'finalizado'
               OR b.estado IS NULL
         GROUP BY a.fecha, 
                  b.id_sol) as c
     GROUP BY c.mes;

mySql no tiene tablas de expresión común, pero seguro que te admite tablas derivadas. Tendrás que cambiar la función del nombre del mes, por la de mysql, y con eso te funcionará.

MySql funciones de fecha

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Answer 10

Amigo, imprime pero el problema es el siguiente este seria el resultado que espero recibir

Mes     solici   orden
January   4        1
February  2        0
Marzo     0        1
April     2        0
June      1        0
December  1        0

y este es el que me tengo 

Mes     solici   orden
January   3        2
February  1        0
April     2        0
June      1        0
December  1        0

Amigo disculpa por molestar tanto me equivoque en la parte de las ordenes en enero solo hay una y la otra esta en marzo

Answer 11

Amigo te explico bien como debi haberlo hecho desde un comienzo necesito imprimir la cantidad de ordenes finalizadas y en que mes se guardaron y cuantas veces y con las solicitudes contarlas todas (porque las solicitudes tambien tiene el campo **ESTADO**) con la primer consulta que me diste esa si imprimia correctamente la cantidad exacta de solicitudes en el mes pero las ordenes solo repetia 2 en todos los meses cuando debia de mostrar Enero 1 y Marzo 1 y esta ultima no se porque me quita un dato en enero y febrero (Perdon por no haber explicado bien a detalle como debi)

Answer 12

Hola Antunez91:

CREATE TABLE solicitud
(id_sol INT, 
 estado VARCHAR(100), 
 fecha  DATE
);
CREATE TABLE orden
(id_sol   INT, 
 id_orden INT, 
 estado   VARCHAR(100)
);
GO
insert into solicitud 
(id_sol, estado, fecha )
values 
(1,'listo','20180101'),
(2,'listo','20180201'),
(3,'listo','20180301'),
(4,'listo','20180401'),
(5,'listo','20180501'),
(6,'listo','20180101'),
(7,'listo','20180201'),
(8,'listo','20180301'),
(9,'listo','20180401'),
(10,'finalizado','20180101'),
(11,'listo','20180301'),
(12,'listo','20180701'),
(13,'finalizado','20180901'),
(14,'listo','20181001');
go

insert into orden 
(id_sol, id_orden, estado )
values 
(1,1,'finalizado'),--cierre en enero
(2,1,'pendiente'),
(4,1,'pendiente'),
(4,2,'finalizado'), --cierre en abril
(6,1,'finalizado'), --cierre en enero
(12,1,'pendiente'),
(12,2,'finalizado'); --cierre en julio
go

Con este escenario:

SELECT c.mesNombre AS Mes, 
       COUNT(c.solicitudes) AS ba, 
       SUM(ordenesCerradas) AS ma
FROM
(
    SELECT(MONTH(a.fecha)) AS mes, 
          datename(month, a.fecha) AS mesNombre, 
          COUNT((a.id_sol)) AS solicitudes,
          CASE
              WHEN b.id_sol IS NULL
              THEN 0
              ELSE 1
          END AS ordenesCerradas
    FROM solicitud a
         LEFT JOIN orden b ON a.id_sol = b.id_sol
    WHERE b.estado = 'finalizado'
          OR b.estado IS NULL
    GROUP BY a.fecha, 
             b.id_sol
) AS c
GROUP BY c.mesNombre, 
         c.mes ORDER BY c.mes;

Salida en SQL Server:

Como podrás observar, la misma cuenta correctamente las órdenes.

Creo que tienes que hacer lo siguiente:

Primero transforma y ejecuta la select interior.

SELECT(MONTH(a.fecha)) AS mes, 
      datename(month, a.fecha) AS mesNombre, 
      COUNT((a.id_sol)) AS solicitudes,
      CASE
          WHEN b.id_sol IS NULL
          THEN 0
          ELSE 1
      END AS ordenesCerradas
FROM solicitud a
     LEFT JOIN orden b ON a.id_sol = b.id_sol
WHERE b.estado = 'finalizado'
      OR b.estado IS NULL
GROUP BY a.fecha, 
         b.id_sol;

case mySql

https://dev.mysql.com/doc/refman/5.7/en/case.html

monthName() mySql

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_monthname

La salida de esta consulta tiene que ser parecida a esta.

A posteriori, donde ya este todo, tienes varias opciones.

Una puede ser ejecutarla dentro de una tabla derivada como el ejemplo que te puse.

Otra puede ser insertar esta consulta en una variable de tabla o una tabla temporal y resolver la salida desde esta variable.

https://code.i-harness.com/es/q/17447a

Pero lo más importante, es que entiendas el código que estas implementando.

Espero te ayude

Answer 13

El lado izquierdo seria el resultado que espero obtener (son resultados de dos consultas separadas) y el de la derecha son los resultado de la consulta que me mostraste la ultima vez.

te agrego las dos consultas 

**orden**

SELECT MONTHNAME( Fecha_ini ) AS Mes, COUNT( Id_orden ) AS Orden
FROM orden_trabajo
WHERE estado = 'Finalizado'
GROUP BY YEAR( fecha_ini ) , MONTH( fecha_ini ) 

**solicitud**

SELECT MONTHNAME( Fecha_soli ) AS Mes, COUNT( Id_soli ) AS Solicitud
FROM solicitud_mante
GROUP BY YEAR( fecha_soli ) , MONTH( fecha_soli ) 

Answer 14

Hola Antunez91:

Te expongo la consulta completa en el entorno mysql, a ver si conseguimos echarla a andar.

create table `solicitud` (id_sol int, 
estado varchar(100),
fecha date );

create table `orden` (id_sol int,
id_orden int,
estado varchar(100),
fecha_ini date);
insert into solicitud(id_sol, estado, fecha)
values
(1,'listo','2018-01-01'),
(2,'listo','2018-02-01'),
(3,'listo','2018-03-01'),
(4,'listo','2018-04-01'),
(5,'listo','2018-05-01'),
(6,'listo','2018-01-01'),
(7,'listo','2018-02-01'),
(8,'listo','2018-03-01'),
(9,'listo','2018-04-01'),
(10,'finalizado','2018-01-01'),
(11,'listo','2018-03-01'),
(12,'listo','2018-07-01'),
(13,'finalizado','2018-09-01'),
(14,'listo','2018-01-01');
insert into orden
(id_sol, id_orden, estado, fecha_ini)
values
(1,1,'finalizado','2018-01-01'),
(2,1,'pendiente','2018-02-01'),
(4,1,'pendiente','2018-03-01'),
(4,2,'finalizado','2018-04-01'),
(6,1,'finalizado','2018-05-01'),
(12,1,'pendiente','2018-06-01'),
(12,2,'finalizado','2018-07-01');

/* Fin del escenario */

Select monthname(a.fecha) as mes, count(*) as solicitud, ifnull(b.orden,0) as orden
from `solicitud` as a
left join (
Select monthname(Fecha_ini) as mes,
count(id_orden) as orden
from `orden`
where estado = 'Finalizado'
group by year(fecha_ini), month(fecha_ini)
) as b on monthname(a.fecha) = b.mes
group by year(a.fecha), month(a.fecha);

La solución pasa por obtener un conjunto interior sobre ordenes con la consulta de ordenes y relacionarlo, con el mes exterior de la tabla de solicitudes.

Ojo como yo no tengo fechas en años diferentes, si tu tienes años, entonces, también debieras de relacionarlos con el año. Entonces el interior tendría year(Fecha_ini) como parte de su select y de su group by, aunque luego nunca se mostrase.

Answer 15

En efecto amigo utilizaba el year, pero todos eran en el mismo año  ahora si ya me muestra los resultados correctamente amigo, mil gracias por tu ayuda eres el mejor y disculpa por las molestias para la próxima seré mas especifico con todo y entender que es lo que necesito, gracias!.

Answer 16

De nada.