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 stackoverflow.com/a/9652359/3630826 — Jaugar Chang
– Jaugar Chang, Commented Oct 3, 2014 at 5:40

Shafeeque · Accepted Answer · 2014-10-03 05:52:51Z

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

Jaugar Chang · Accepted Answer · 2014-10-03 05:59:20Z

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

Collectives™ on Stack Overflow

Get the count of data based on id in mysql result

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related