Simple Count Mysql query

Question

receipt_id           order_id
1                       A
2                       B
3                       B
4                       C
5                       C

What is the sql code to show all the records in this table where order_id's count is more than 1?

Taryn · Accepted Answer · 2012-10-12 18:59:31Z

3

You just need an aggregate function with a GROUP BY

select order_id
from yourtable
group by order_id
having count(order_id) > 1

See SQL Fiddle with Demo

answered Oct 12, 2012 at 18:59

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

Comments

Ankur · Accepted Answer · 2012-10-12 19:00:22Z

1

Try this:

 Select * from table where order_id in (select  distinct order_id
 from table
 group by order_id
 having count(order_id) > 1)

DEMO

answered Oct 12, 2012 at 19:00

Ankur

12.8k7 gold badges40 silver badges68 bronze badges

2 Comments

Manish Pradhan Over a year ago

Thanks a lot. Although it takes a lot of time to execute in a large table.

Ankur Over a year ago

just create an index on order_id

Collectives™ on Stack Overflow

Simple Count Mysql query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related