Postgresql Nombre de comptes multiples pour une table

De deux colonnes dans ma table, je veux obtenir un unifiée prise en compte pour les valeurs dans ces colonnes.
Comme un exemple, les deux colonnes sont:

Table: rapports

|   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 je fais:
SÉLECTIONNEZ le type, count(*) à partir de rapports
groupe par type

J'obtiens:

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

Im essayant d'obtenir quelque chose comme ceci: (une colonne la plus à droite avec mes types regroupés et plusieurs colonnes avec le comte de vallées pour chaque lieu)
J'obtiens:

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

qui serait le résultat de l'exécution d'un comte comme celle ci-dessus pour chaque endroit comme celui-ci:

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

Est-ce possible avec postgresql?

Merci d'avance.

source d'informationauteur Chuydb

vous pouvez utiliser case dans ce cas -

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

Il faut résoudre votre problème

@S T Mohammed,
Pour obtenir un tel type nous pouvons simplement utiliser using après group ou where condition dans la requête externe, comme ci-dessous -

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

Réponse par praktik garg est correct, il n'est pas nécessaire d'utiliser 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

Vous pouvez également utiliser la même syntaxe plus courte:

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

Cela fonctionne parce que la valeur booléenne true est coulé à 1 si la condition est remplie.

Vous pouvez utiliser la clause de filtre:

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

Vous devez vous connecter pour publier un commentaire.