user3307783 user3307783 - 1 year ago 58
MySQL Question

Count result matching in row not all

I'm trying to get the last matching in column 'more' of result VG1.

In this case my query is counting all the results in the column, but I need just to count before its changed. If its VG1, VG1, VG2, VG1 I need result 2, not 3 ( all ).


SELECT home, count(*) as count from results where more='VG2' and home='pero' group by home order by count(more) desc

MYSQL schema:

Answer Source

You could use variables for this:

select   home, more, count(*) as count 
from     (select   *,
                   @set := if(@home <> home, '', @set),
                   @home := home,
                   not find_in_set(more, @set) as is_first,
                   @set := if (@more <> '' and @more <> more, 
                               concat(@set, @more, ','), 
                   @more := more
          from     results,
                   (select @set:='', @prev:='', @home='') init
          order by home, datum desc) base
where is_first
group by home, more 
order by count(*) desc


| home | more | count |
| pero |  VG1 |     2 |
| pero |  VG2 |     1 |

See fiddle

The is_first column from the inner query indicates whether a record belongs to the first bunch of more values per home. This is put as a condition in the outer query, so that the count function returns the required result.

If you need only the result for a certain home and more value, then just add that in a where clause in the outer query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download