Languages
[Edit]
EN

MySQL - groups count using group by

6 points
Created by:
maxsior322
227

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

Quick solution (wrap your SQL query with COUNT() querry):

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