Get the count of data based on id in mysql result

Question

I have a table like the below one

id  |  id_fk  |  data  |
-------------------------
1   |    2    | data1  |
2   |    2    | data2  |
3   |    1    | data3  |
4   |    3    | data4  |
5   |    1    | data5  |
-------------------------

here I have the table id as 'id', foreign key from another table as id_fk.

What I try to achieve is, to get the count of each foreign key in an increment mode. that is, if the id_fk -> 2 occur on the first time, then the count should be 1, at the next occurance count become 2, and so on for all the id_fk. I tried many ways. But none give me the actual output.

From the above table, the result table will look like:

id_fk  |  count  | 
------------------
1      |    1    | 
1      |    2    | 
2      |    1    | 
2      |    2    | 
3      |    1    | 
------------------

Please help me to solve this.. any help will be appreciated.

You want something like row_number. see http://stackoverflow.com/a/9652359/3630826 — Jaugar Chang, Oct 03 '14 at 05:40

score 2 · Answer 1 · answered Oct 03 '14 at 05:52

2

Try this

SELECT `id_fk`,
@a:=IF(id_fk=@b,@a+1,1) serial_number,
@b:=id_fk
FROM your_table,(SELECT @a:= 0,@b:=0) AS a 
ORDER BY `id_fk` ASC

answered Oct 03 '14 at 05:52

Shafeeque

2,039
2
13
28

score 1 · Accepted Answer · answered Oct 03 '14 at 05:59

1

It works perfect with join.

select t1.id_fk,t1.id,count(*)
  from your_table t1
  left join your_table t2
    on t1.id_fk=t2.id_fk and t1.id>=t2.id
 group by  t1.id_fk,t1.id

See Sql Fiddle Demo

answered Oct 03 '14 at 05:59

Jaugar Chang

3,176
2
13
23

Get the count of data based on id in mysql result

2 Answers2