Seleccione count ( * ) de varias tablas
¿Cómo puedo seleccionar count(*)
de dos tablas diferentes (llamarlas tab1
y tab2
) teniendo como resultado:
Count_1 Count_2
123 456
He intentado esto:
select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2
Pero todo lo que tengo es:
Count_1
123
456
18 answers
SELECT (
SELECT COUNT(*)
FROM tab1
) AS count1,
(
SELECT COUNT(*)
FROM tab2
) AS count2
FROM dual
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-03-05 09:52:36
Como información adicional, para lograr lo mismo en SQL Server, solo necesita eliminar la parte "DE dual" de la consulta.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-08-18 21:44:44
Solo porque es ligeramente diferente:
SELECT 'table_1' AS table_name, COUNT(*) FROM table_1
UNION
SELECT 'table_2' AS table_name, COUNT(*) FROM table_2
UNION
SELECT 'table_3' AS table_name, COUNT(*) FROM table_3
Da las respuestas transpuestas (una fila por tabla en lugar de una columna), de lo contrario no creo que sea muy diferente. Creo que en términos de rendimiento deberían ser equivalentes.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-03-03 16:51:29
Mi experiencia es con SQL Server, pero podría hacer:
select (select count(*) from table1) as count1,
(select count(*) from table2) as count2
En SQL Server obtengo el resultado que buscas.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-03-03 12:45:31
Otros métodos ligeramente diferentes:
with t1_count as (select count(*) c1 from t1),
t2_count as (select count(*) c2 from t2)
select c1,
c2
from t1_count,
t2_count
/
select c1,
c2
from (select count(*) c1 from t1) t1_count,
(select count(*) c2 from t2) t2_count
/
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-03-03 20:25:18
Como no puedo ver ninguna otra respuesta sacar esto.
Si no le gustan las sub-consultas y tienen claves primarias en cada tabla, puede hacer esto:
select count(distinct tab1.id) as count_t1,
count(distinct tab2.id) as count_t2
from tab1, tab2
Pero en cuanto al rendimiento, creo que la solución de Quassnoi es mejor, y la que usaría.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-03-05 10:09:26
Aquí es de mí para compartir
Opción 1-contar desde el mismo dominio desde una tabla diferente
select distinct(select count(*) from domain1.table1) "count1", (select count(*) from domain1.table2) "count2"
from domain1.table1, domain1.table2;
Opción 2-contar desde diferentes dominios para la misma tabla
select distinct(select count(*) from domain1.table1) "count1", (select count(*) from domain2.table1) "count2"
from domain1.table1, domain2.table1;
Opción 3-contar desde un dominio diferente para la misma tabla con "union all" para tener filas de conteo
select 'domain 1'"domain", count(*)
from domain1.table1
union all
select 'domain 2', count(*)
from domain2.table1;
Disfruta del SQL, siempre lo hago :)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-02-24 17:27:04
select (select count(*) from tab1) count_1, (select count(*) from tab2) count_2 from dual;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-03-03 12:39:54
SELECT (SELECT COUNT(*) FROM table1) + (SELECT COUNT(*) FROM table2) FROM dual;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-01-11 13:21:53
Una puñalada rápida surgió con:
Select (select count(*) from Table1) as Count1, (select count(*) from Table2) as Count2
Nota: Probé esto en SQL Server, por lo que From Dual
no es necesario (de ahí la discrepancia).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-08-18 21:43:55
select
t1.Count_1,t2.Count_2
from
(SELECT count(1) as Count_1 FROM tab1) as t1,
(SELECT count(1) as Count_2 FROM tab2) as t2
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-06-02 22:07:27
Si las tablas (o al menos una columna clave) son del mismo tipo, simplemente haga la unión primero y luego cuente.
select count(*)
from (select tab1key as key from schema.tab1
union all
select tab2key as key from schema.tab2
)
O toma tu satement y pon otra suma() a su alrededor.
select sum(amount) from
(
select count(*) amount from schema.tab1 union all select count(*) amount from schema.tab2
)
Para un poco de integridad - esta consulta creará una consulta para darle un recuento de todas las tablas para un propietario dado.
select
DECODE(rownum, 1, '', ' UNION ALL ') ||
'SELECT ''' || table_name || ''' AS TABLE_NAME, COUNT(*) ' ||
' FROM ' || table_name as query_string
from all_tables
where owner = :owner;
La salida es algo así como
SELECT 'TAB1' AS TABLE_NAME, COUNT(*) FROM TAB1
UNION ALL SELECT 'TAB2' AS TABLE_NAME, COUNT(*) FROM TAB2
UNION ALL SELECT 'TAB3' AS TABLE_NAME, COUNT(*) FROM TAB3
UNION ALL SELECT 'TAB4' AS TABLE_NAME, COUNT(*) FROM TAB4
Que luego puede ejecutar para obtener sus cuentas. Es sólo un guión práctico para tener alrededor a veces.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-08-18 21:46:48
Declare @all int
SET @all = (select COUNT(*) from tab1) + (select count(*) from tab2)
Print @all
O
SELECT (select COUNT(*) from tab1) + (select count(*) from tab2)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-08-03 19:14:06
--============= FIRST WAY (Shows as Multiple Row) ===============
SELECT 'tblProducts' [TableName], COUNT(P.Id) [RowCount] FROM tblProducts P
UNION ALL
SELECT 'tblProductSales' [TableName], COUNT(S.Id) [RowCount] FROM tblProductSales S
--============== SECOND WAY (Shows in a Single Row) =============
SELECT
(SELECT COUNT(Id) FROM tblProducts) AS ProductCount,
(SELECT COUNT(Id) FROM tblProductSales) AS SalesCount
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-28 12:11:30
UNIRSE con diferentes tablas
SELECT COUNT(*) FROM (
SELECT DISTINCT table_a.ID FROM table_a JOIN table_c ON table_a.ID = table_c.ID );
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-08-16 05:35:20
Seleccionar
(seleccione count () desde tab1 donde field
como 'value') +
(seleccione count () desde tab2 donde field
como 'value')
count
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-24 20:11:04
select @count = sum(data) from
(
select count(*) as data from #tempregion
union
select count(*) as data from #tempmetro
union
select count(*) as data from #tempcity
union
select count(*) as data from #tempzips
) a
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-06-25 01:10:40