Count quantities of a given attribute within a table

Question

all right?

I have the following question, I created a database for a card game in the style of Magic. I have a table that shows the skills and another that shows the cards and, another one that I called a skill card that shows the card and its respective skill.

I would like to list the skills and how many cards have a certain skill. For example, the ability to disappear presents 3 cards that have it. Below is the code, but gives the following error: "unknow column 'ch.idcarta' in 'Where clause'

How can I solve this problem? Thanks for your help.

select h.nome, count(h.nome)as quantidade
from carta_habilid
inner join habilidades h on ch.idhabilidades=h.idhabilidades
where ch.idcarta=c.idcarta 
group by h.nome;

Answer 1

It’s just missing to tell the query who is the ch you want to reference:

from carta_habilid ch
inner join habilidades h on ch.idhabilidades=h.idhabilidades
where ch.idcarta=c.idcarta 
group by h.nome;