FBB FBB - 6 months ago 9
SQL Question

Is it possible to get results, and count of the results, at the same time? (to filter results based on the result count)

I have a query for which I want to obtain results, and count of the results, at the same time. The filtering of the results is complex, so I can not simply use the subquery trick as in this other question. My ultimate goal is to filter the results based on the result count.

Example:

SELECT id, related_info, count(related_info)
FROM my_table
WHERE <complex filtering on related_info here>;


Results should look like:
id | related_info | count(related_info)|
1 | info1| 3|
1 | info2| 3|
1 | info3| 3|
2 | info1| 2|
2 | info2| 2|


My ultimate goal is to filter the results based on the count, for example:

SELECT id, related_info, count(related_info)
FROM my_table
WHERE <complex filtering on related_info here> having count(related_info) >=3;`


Results should look like:
id | related_info | count(related_info)|
1 | info1| 3|
1 | info2| 3|
1 | info3| 3|

(results for
id
2 are filtered)

I can not use
group by
because I want to get all the results. I can not use a subquery, because it would imply to perform the complex filtering twice.

I don't see any way to perform this with a single query.

Answer

The following query:

SELECT id, related_info, count(related_info)
FROM my_table
WHERE <complex filtering on related_info here>
group by id, related_info with rollup

would produce results like:

id | related_info |  count(related_info)|
1  |         info1|                    1|
1  |         info2|                    1|
1  |         info3|                    1|
1  |         NULL |                    3|

rollup adds an extra row with the summary information.

The solution is easy in most databases:

SELECT id, related_info, count(related_info) over (partition by id)
FROM my_table
WHERE <complex filtering on related_info here>

Getting the equivalent in MySQL without repeating the where clause is challenging.

A typical alternative in MySQL, if you need the list of "related_info" is to use group_concat:

select id, group_concat(related_info), count(*)
from my_table
where <complex filtering on related_info here>
group by id;

And a final method, assuming that related_info is a single column that uniquely identifies each row:

select mt.id, mt.related_info, t.cnt
from my_table mt join
     (select id, group_concat(related_info) as relatedInfoList, count(*) as cnt
      from my_table
      where <complex filtering on related_info here>
      group by id
     ) t
     on mt.id = t.id and
        find_in_set(related_info, relatedInfoList) > 0

This turns "related_info" into a list and then matches back to the original data. This can also be done with a unique id in the original data (which id is not based on the sample data).

Comments