How to query for getting count of a column with different values

Question

I have written a query in which i am getting result like this.

nid  | target_id
2570 | 748
2570 | 756
2570 | 756
2570 | 756
2571 | 799
2572 | 785

I have tried count and group by but my result is like

nid  | target_id
2570 | 4
2571 | 1
2572 | 1

Now I want to write some query to get result something like this

nid  | target_id | count(target_id)
2570 | 748       | 1
2570 | 756       | 3
2571 | 799       | 1
2572 | 785       | 1

And then get all the nid whose count is greater than 1. I have tried many queries but not find a luck to find the solution. Any help will be appreciated

Think group by with having to filter out the cases where count is greater than 1. Also, do a group by of nid and target_id — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 18, 2018 at 12:16

Zaynul Abadin Tuhin · Accepted Answer · 2018-10-18 12:27:40Z

1

use count() and both column in group by

select nid,target_id,count(*) as count_target_id from t
group by nid,target_id
having count_target_id>1

edited Oct 18, 2018 at 12:27

answered Oct 18, 2018 at 12:14

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

1 Comment

Mahtab Alam Over a year ago

Thanks this was the query which i wanted. You saved my day

Collectives™ on Stack Overflow

How to query for getting count of a column with different values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related