Group 2 count () in a query in PostgreSQL

Question

Group 2 count () in a query in PostgreSQL

Navigation

#1 by (1 votes)

2

These are the tables in my database:

animal

 _________________
|id_animal | zona |
|__________|______|
| T1       | 01   |
|__________|______|
| B1       | 01   |
|__________|______|
| C1       | 01   |
|__________|______|
| C2       | 01   |
|__________|______|
| B1       | 02   |
|__________|______|

donation

 _______________________________
|id_animal | c_especie | status |
|__________|___________|________|
| T1       | 1         | 0      |
|__________|___________|________|
| B1       | 2         | 1      |
|__________|___________|________|
| C1       | 3         | 1      |
|__________|___________|________|
| C2       | 3         | 0      | 
|__________|___________|________|
| B1       | 2         | 1      |
|__________|___________|________|

species

 ___________________________
|c_especie | nombre_especie |
|__________|________________|
|  1       | felinos        |
|__________|________________|
|  2       | anfibios       |
|__________|________________|
|  3       | reptiles       |
|__________|________________|

I need a query that shows the results of the total of donations and the total of animals that have not received a donation grouped name of species FROM ZONE 01, in the table donations the column status refers to 1 (donation) and 0 ( without donation), that is, the desired result is as follows

 ________________________________________________
| nombre_especie | Total_donaciones | Sin_apoyo  |
|________________|__________________|____________|
| felinos        | 0                |  1         |
|________________|__________________|____________|
| anfibios       | 1                |  0         |
|________________|__________________|____________|
| reptiles       | 1                |  1         |
|________________|__________________|____________|

This is the code that gets the first 2 columns (name_species | Total_donations), but I do not know how to nest the third column (Sin_support)

select e.nombre_especie, count (d.id_animal) AS Total_donaciones 
from especie e, donacion d, animal a 
where e.c_especie = d.c_especie
      and d.id_animal = a.id_animal
      and d.status = 1 
      and a.zona = 01
group by e.nombre_especie
order by e.nombre_especie ASC;

I'm working on postgres, I thank you in advance.

sql postgresql

asked by IndiraRivas 19.04.2018 в 01:33

source

1 answer

Variables in global python 3 Linq adds a nonexistent column to the SQL query

score 1 · Accepted Answer

I already have the query you need, I hope it will be helpful and help many animals, greetings.

select e.c_especie, e.nombre_especie, count(d.'status') as sum,
(
    select count(*) 
    from donacion d2, animal a2
    where d2.'status'=0 and d2.c_especie=e.c_especie and a2.zona=01 and a2.id_animal=d2.id_animal
) AS sin_apoyo

from (animal a inner join donacion d
on a.id_animal=d.id_animal and a.zona=01 and d.'status'=1) 
right join especie e on e.c_especie=d.c_especie
group by d.c_especie
ORDER BY e.nombre_especie