I can't seem to get my logic straight today so was hoping somebody could point out what I'm screwing up on. I have 2 table but I want to join one of them to the other twice. The first time I join it I want to count how many times columnA = 'X' and the second time I join it I want it to count how many times ColumnA = 'Y'.
select count(*), a.Column, b.column, c.name
from tablea as a, tablea as b, names as c
where a.id = c.id
and b.id = c.id
group by c.name
I've tried different variations of this and I've spent a while building the query up from a basic query but it fails as soon as I bring the same table in twice.