sql - Count rows using group by and display all rows MYSQL PHP -

i have sql table this:

id     fname     cat                  address        status  1      bash      wedding venue        abc            2      bash      wedding venue        bcd            3      jash      wedding venue        abc            4      hash      wedding venue        bcd               5      rash      wedding card         bcd            

i want fetch results having cat value wedding venue , count duplicate fname. query using this, , working fine.

select *, count(*) counts table cat='wedding venue' , status='a' group fname; 

output:

id     fname   count(*)     cat                address      status 1      bash    2            wedding venue      abc          3      jash    1            wedding venue      abc          4      hash    1            wedding venue      bcd          

is there possible way display output this:

id     fname   count(*)     cat                address      status 1      bash    2            wedding venue      abc          2      bash    2            wedding venue      bcd          3      jash    1            wedding venue      abc          4      hash    1            wedding venue      bcd          

we have different definitions of 'working fine', may little tricky me help.

but query result you're after.

drop table if exists my_table;  create table my_table (id int not null auto_increment primary key ,fname varchar(12) not null ,cat varchar(20) not null ,address varchar(12) not null ,status char(1) not null );  insert my_table values (1,'bash','wedding venue','abc','a'), (2,'bash','wedding venue','bcd','a'), (3,'jash','wedding venue','abc','a'), (4,'hash','wedding venue','bcd','a'), (5,'rash','wedding card','bcd','a');  select x.*      , y.count    my_table x    join       ( select fname             , cat             , status             , count(*) count           my_table          group             fname             , cat             , status      ) y      on y.fname = x.fname     , y.cat = x.cat     , y.status = x.status   x.cat = 'wedding venue'     , x.status = 'a'; +----+-------+---------------+---------+--------+-------+ | id | fname | cat           | address | status | count | +----+-------+---------------+---------+--------+-------+ |  1 | bash  | wedding venue | abc     |      |     2 | |  2 | bash  | wedding venue | bcd     |      |     2 | |  3 | jash  | wedding venue | abc     |      |     1 | |  4 | hash  | wedding venue | bcd     |      |     1 | +----+-------+---------------+---------+--------+-------+