I have the following SQL query:
SELECT DISTINCT `c1` FROM `table1` AS Column1 INNER JOIN `table2` ON `table1`.`c2`=`table2`.`c2`
Above query returns some distinct rows from `table1`.
Now for each row(value of c1 column) returned by the above query, I want to count the rows from `table2` which have column `c1` value equal to to the above query returned value. for example, for a single row returned by the above query, I have a returned value 'A'.
Now the single query for this row would be something like this:
SELECT COUNT(*) FROM `table2` WHERE `table2`.`c1` = A
Now, being new to SQL, my question is, How can I mix both of the above queries in one single query.
In a way that,for example, in a row returned by the final(mixed) query,I have a column returning 'A' value and another column returning the number of rows of `table2` having column `c1` with value of 'A'?
I hope I have explained my question clearly.
Any help with this problem would be highly appreciated.
Thanks in advance.