Postgresql Múltiples counts para una tabla

De dos columnas en mi tabla, quiero get un recuento unificado de los valores en estas columnas. Como un ejemplo, dos columnas son:

Tabla: informes

| type | place | ----------------------------------------- | one | home | | two | school | | three | work | | four | cafe | | five | friends | | six | mall | | one | work | | one | work | | three | work | | two | cafe | | five | cafe | | one | home | 

Si lo hago: SELECCIONE el tipo, cuente (*) del grupo de informes por tipo

Yo obtengo:

 | type | count | ----------------------------- | one | 4 | | two | 2 | | three | 2 | | four | 1 | | five | 2 | | six | 1 | 

Estoy tratando de get algo como esto: (una columna a la derecha con mis types agrupados y varias columnas con los valores de conteo para cada lugar) Obtengo:

 | type | home | school | work | cafe | friends | mall | ----------------------------------------------------------------------------------------- | one | 2 | | 2 | | | | | two | | 1 | | 1 | | | | three | | | 2 | | | | | four | | | | 1 | | | | five | | | | 1 | 1 | | | six | | | | | | 1 | 

que sería el resultado de ejecutar un recuento como el de arriba para cada lugar como este:

 SELECT type, count(*) from reports where place = 'home' group by type SELECT type, count(*) from reports where place = 'school' group by type SELECT type, count(*) from reports where place = 'work' group by type SELECT type, count(*) from reports where place = 'cafe' group by type SELECT type, count(*) from reports where place = 'friends' group by type SELECT type, count(*) from reports where place = 'mall' group by type 

¿Es esto posible con postgresql?

Gracias por adelantado.

puedes usar el case en este caso –

 SELECT type, sum(case when place = 'home' then 1 else 0 end) as Home, sum(case when place = 'school' then 1 else 0 end) as school, sum(case when place = 'work' then 1 else 0 end) as work, sum(case when place = 'cafe' then 1 else 0 end) as cafe, sum(case when place = 'friends' then 1 else 0 end) as friends, sum(case when place = 'mall' then 1 else 0 end) as mall from reports group by type 

Debería resolver tu problema

@ST Mohammed, para get ese tipo podemos simplemente usar el using después del group o la condición en la consulta externa, como se muestra a continuación:

 select type, Home, school, work, cafe, friends, mall from ( SELECT type, sum(case when place = 'home' then 1 else 0 end) as Home, sum(case when place = 'school' then 1 else 0 end) as school, sum(case when place = 'work' then 1 else 0 end) as work, sum(case when place = 'cafe' then 1 else 0 end) as cafe, sum(case when place = 'friends' then 1 else 0 end) as friends, sum(case when place = 'mall' then 1 else 0 end) as mall from reports group by type ) where home >0 and School >0 and Work >0 and cafe>0 and friends>0 and mall>0 

La respuesta de praktik garg es correcta, no es necesario usar else 0 :

 SELECT type, sum(case when place = 'home' then 1 end) as home, sum(case when place = 'school' then 1 end) as school, sum(case when place = 'work' then 1 end) as work, sum(case when place = 'cafe' then 1 end) as cafe, sum(case when place = 'friends' then 1 end) as friends, sum(case when place = 'mall' then 1 end) as mall, from reports group by type 

También puede usar la siguiente syntax aún más corta:

 SELECT type, sum((place = 'home')::int) as home, sum((place = 'school')::int) as school, sum((place = 'work' )::int) as work, sum((place = 'cafe' )::int) as cafe, sum((place = 'friends')::int) as friends, sum((place = 'mall')::int) as mall, from reports group by type 

Esto funcionará porque boolean true se convierte a 1 cuando se cumple la condición.

También puede usar la cláusula de filter:

 SELECT type, sum(1) FILTER (WHERE place = 'home') AS home, sum(1) FILTER (WHERE place = 'school') AS school, sum(1) FILTER (WHERE place = 'work') AS work, sum(1) FILTER (WHERE place = 'cafe') AS cafe, sum(1) FILTER (WHERE place = 'friends') AS friends, sum(1) FILTER (WHERE place = 'mall') AS mall FROM reports GROUP BY type