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?
 
Dale FyeCommented:
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
 
Dale FyeCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
awking00Commented:
>>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 FyeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.