CODEWITHSUNDEEP
mysqlsql
FBB
FBB

Reputation: 1465

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.

Upvotes: 0

Views: 155

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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

Upvotes: 1

Marius
Marius

Reputation: 23

Try using the Count analytical function. The syntax is COUNT(*) OVER (PARTITION BY ...). You can find more here: http://msdn.microsoft.com/en-us/library/ms189461.aspx

Upvotes: 0

Related Questions