Languages
[Edit]
EN

MySQL - groups count using group by

6 points
Created by:
AnnLen
13390

In this short article, we would like to show how to count detected groups in MySQL query that uses GROUP BY statement. 

Quick solution:

SELECT COUNT(*) as `groups_count`
FROM (
    SELECT COUNT(t2.table_1_id) as `group_size`
    FROM table_1 t1
    INNER JOIN table_2 t2 ON t1.id = t2.table_1_id
    WHERE t1.`some_column` = 'some value'
    GROUP BY t2.table_1_id
) AS wrapper;

 

Alternative solution

This solution uses partitioning to find a number of detected groups.

SELECT SUM(COUNT(DISTINCT t2.table_1_id)) OVER() as `groups_count`
FROM table_1 t1
INNER JOIN table_2 t2 ON t1.id = t2.table_1_id
WHERE t1.`some_column` = 'some value'
GROUP BY t2.table_1_id
LIMIT 1

Where: LIMIT 1 prevents unnecessary rows duplication in the returned results. 

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join