Search code examples
mysqldatatablecount

how to add another column to query result?

i need some help.

so i have one table like this.

| id_num | payment | dollar |

| 889 | 1/2/2022 | 500 |

| 889 | 3/2/2022 | 400 |

| 789 | 1/2/2022 | 350 |

| 123 | 2/1/2022 | 200 |

I want to count how many unique Id_num with their payment date.

i'm using this query

select id_num, count(*) as count from tabletesting Group By id_num ;

And the result was

| id_num | count |

| 889 | 2 |

| 789 | 1 |

| 123 | 1 |

What i want is something like this

| id_num | payment | count |

| 889 | 1/2/2022 | 2 |

| 889 | 3/2/2022 | 2 |

| 789 | 1/2/2022 | 1 |

| 123 | 2/1/2022 | 1 |

how could i achieve that result ?

Many Thanks.

Solution

  • u can try this query

    select id_num, payment,
(
select count(*) as count 
from tabletesting b
where b.id_num = a.id_num
group by id_num
) as 'count'
from tabletesting a
group by id_num, payment

    I hope it help