Okay, so let's say I have two tables, "TableA" and "TableB". In Table A I have a column "C", and in Table B I have a column "D" and a column "E".
TableA contains a lot of rows that can contain the same value in column C. TableB contains a lot of rows, but column D has to be unique (it's the autoincrement number). TableB's column E also can contain the same value in different rows. As well, column C and column D will contain the same numbers.
Example Tables
TableA
ID C
----------
1 1
2 1
3 2
TableB
D E
----------
1 1
2 0
3 0
Now, what I want to do is count all the rows in TableA in column C, which I have figured out how to do. However, I additionally want it to only give me as a result the count of C where the equivalent value in D also has an E of "1".
So, I have tried this, but it is failing and I cannot think of what I'm doing wrong or how to fix it. Googling for COUNT only gets me results dealing with one table.
In my example, I want it to COUNT C where the ID number that matches in TableB column D where E = 1 (so, where TableB column D = 1), and return that as the count, so I want it to return to me (or something to this effect):
c COUNT(*) Descending 1 2
SELECT TableA.C, COUNT(*) FROM TableA, TableB WHERE TableB.E = 1 GROUP BY TableA.C ORDER BY COUNT(*) DESC
If that makes any sense...
You don't join the two tables on the column
SELECT a.C, COUNT(*) FROM TableA a JOIN TableB b ON (C = D)
WHERE E GROUP BY a.C
ORDER BY COUNT(*) DESC