Sai Kiran Veeraneni Sai Kiran Veeraneni - 4 months ago 7
SQL Question

Select *FROM table two and count(coulmn_name) FROM table one

How can i select * FROM table two and count(t_type) FROM table one WHERE species in table one and two are equal

Table one = trees

id | t_type
~~~~~~~~~~~~
1 | Tree one
2 | Tree two
3 | Tree Three
4 | Tree Four
5 | Tree one


Table two = requests

id | req_species
~~~~~~~~~~~~
1 | Tree one
2 | Tree two
3 | Tree one
4 | Tree two


The returned table will have same number of rows as of table two (requests), in this case 4 rows.

Expected Output

species | Qunatity
~~~~~~~~~~~~
Tree one | 2
Tree two | 1
Tree one | 2
Tree two | 1

Answer

One method uses a correlated subquery:

select r.species,
       (select count(*) from trees t where t.species = r.species) as quantity
from requests r;