CODEWITHSUNDEEP
mysql
Arun
Arun

Reputation: 3731

Get the count of data based on id in mysql result

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.

Upvotes: 0

Views: 62

Answers (2)

Jaugar Chang
Jaugar Chang

Reputation: 3196

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

Upvotes: 1

Shafeeque
Shafeeque

Reputation: 2069

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

Upvotes: 2

Related Questions