mysql COUNT results with GROUP BY

Question

I have a table with with 2 unique linked table ids. I get the results I want with GROUP BY but when I count I only get the number of each group.

When I do:

SELECT COUNT(id) FROM my_table GROUP BY first_linked_table_id, second_linked_table_id

I get as results 1, 2, 3, 1 but I want 4 as a result.

I tried DISTINCT but I think that only works with one column

Answer 1

Your requirement is to get count of number of groups. So we need two operations-

1. Group(inner query)
2. Count(outer query)

Following query will do precisely that:

SELECT COUNT(*)
FROM
(
    SELECT COUNT(id) 
    FROM my_table 
    GROUP BY first_linked_table_id,
             second_linked_table_id
) t

Answer 2

If you want to count the rows, I think you're going to need a subquery. Something like this:

SELECT COUNT(*) FROM (
    SELECT COUNT(id) FROM my_table GROUP BY first_linked_table_id, second_linked_table_id
);