SQL GROUP BY

Hi, i have custid and orderid.

I want to find out the custid and orderid where a customer has 4 orders.

I want get custid and orderid in the query.

Can anyone help with this query plz..

thanks
gvamsimbaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
SELECT T.CustID
FROM (
SELECT CustID, OrderID
FROM yourTable
Group BY CustID, OrderID
) as T
GROUP BY T.CustID
HAVING Count(OrderID) = 4
0
gvamsimbaAuthor Commented:
Hi Dale, I want to get BOTH custid and orderid in my result set.

Thanks
0
Dale FyeOwner, Developing Solutions LLCCommented:
You have to go one step further then.

SELECT T2.CustID, T2.OrderID
FROM yourTable as T2
INNER JOIN (
SELECT T.CustID
FROM (
SELECT CustID, OrderID
FROM yourTable
Group BY CustID, OrderID
) as T
GROUP BY T.CustID
HAVING Count(OrderID) = 4
) as T1 ON T2.CustID = T1.CustID

Don't know what database you are using.  There is probably a more elegant (and quicker) way in SQL Server, but I generally work in Access, and this should work in almost any RDBMS
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

awking00Information Technology SpecialistCommented:
>>I want to find out the custid and orderid where a customer has 4 orders.
 I want get custid and orderid in the query.
<<

So, in the case where a customer has 4 orders, you want to get the custid and all 4 orderids returned? Would the same hold true if a customer had more than 4 orders? It will help if you can provide some sample data and the expected output.
0
gvamsimbaAuthor Commented:
nice one.
0
Dale FyeOwner, Developing Solutions LLCCommented:
glad to help

But in the future, you might want to indicate what type of database (Access, SQL Server, MySQL , Oracle, ...) you are querying, as syntax varies.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.