CODEWITHSUNDEEP
phpmysqlsqlcount
Developer
Developer

Reputation: 847

Counting rows for some predefined data

I have a data set :

id label date
1  B     2014-02-15
1 NB     2014-02-16
1 B      2014-02-17
1 B      2014-02-18

I want to count(id) if for current id the previous label was B e.g. i would like to have following output:

label count(id)
B     1
NB    1

So far I have tried the following query:

select a.label,count(a.id)
from test a
WHERE a.date <(select max(b.date) from test b
                where b.label= 'B'
                ) 
group by 1

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

You need to get the previous label. I would do this with a correlated subquery, and then do the aggregation:

select label, count(*)
from (select t.*,
             (select t2.label
              from test t2
              where t2.date < t.date
              order by t2.date desc
              limit 1
             ) as prevlabel
      from test t
     ) t
where prevlabel = 'B'
group by label;

Upvotes: 1

Related Questions