Mysql count duplicate value different column

Question

I want to count the same value among parent_id and id_article, but it can be 0 if there is no same value among parent_id and id_article

table:t_article
id_article      parent_id
441             0
1093            18
18              0
3141            3130
3130            0
3140            3130
3142            3130

Expected output

id_article      parent_id       Total
441             0               0
1093            18              0
18              0               1
3141            3130            0
3130            0               3
3140            3130            0
3142            3130            0

How do I make it happen?

Please edit your question to show the code you have so far. Most of us here are happy to help you improve your craft, but are less happy acting as short-order unpaid programming staff. Show us your work so far as a minimal reproducible example, the result you were expecting and the results you got, and we'll help you figure it out. It may help to re-read How to Ask. — Toby Speight
– Toby Speight, Commented Jun 6, 2018 at 8:11

M Khalid Junaid · Accepted Answer · 2018-06-06 08:08:44Z

1

You can get your count by doing a sub clause and then join with your main query

select a.*, coalesce(b.cnt,0)
from t_article a
left join (
  select parent_id, sum(parent_id <> 0) cnt
  from t_article 
  group by parent_id
) b on (a.id_article = b.parent_id)

Demo

answered Jun 6, 2018 at 8:08

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql count duplicate value different column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related