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

No, DISTINCT works with any number of columns, but it won't change much as DISTINCT is usually implemented by GROUP BY under the hood. Please include some sample data here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 21, 2017 at 8:49
@TimBiegeleisen DISTINCT only returns the unique values of one specific column, what I want is every row where the combination first_linked_table_id and second_linked_table_id is unique — Paul Roefs
– Paul Roefs, Commented Feb 21, 2017 at 9:06
No, you're wrong. You can use DISTINCT field1, field2, but in any case this is irrelevant to your question. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 21, 2017 at 9:07
I know DISTINCT field1, field 2 is possible but it's not what I'm looking for. That returns 1, 1 and 2, 1 where I also want 1, 2 and 1, 3 — Paul Roefs
– Paul Roefs, Commented Feb 21, 2017 at 9:15

Bhavesh Ghodasara · Accepted Answer · 2019-10-18 13:27:19Z

54

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

Group(inner query)

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

edited Oct 18, 2019 at 13:27

answered Feb 21, 2017 at 8:49

Bhavesh Ghodasara

2,0813 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Paul Roefs Over a year ago

it returns the error: Every derived table must have its own alias

Paul Roefs Over a year ago

sorry, I tried using an enter submitting my comment when it wasnt ready. I edited it

mickmackusa Over a year ago

Try This answers are low-value on StackOverflow because they do a poor job of educating/empowering the OP and thousands of future researchers about how your solution works and why it is a good solution. Please take a moment to explain your answer so that it is more valuable to readers and role models better posting behavior to new users.

Claudio Venturini · Accepted Answer · 2017-02-21 08:49:24Z

6

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
);

answered Feb 21, 2017 at 8:49

Claudio Venturini

6985 silver badges15 bronze badges

Collectives™ on Stack Overflow

mysql COUNT results with GROUP BY

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related