Home » SQL & PL/SQL » SQL & PL/SQL » Avoiding 2 Group By clauses
Avoiding 2 Group By clauses [message #660826] Mon, 27 February 2017 09:28 Go to next message
manikandan23
Messages: 25
Registered: February 2017
Junior Member
I want the faster method of returning the customers who purchased one and only one product id.

cust_id order_date prod_id units
C1 01-JAN-00 P1 10
C2 01-JAN-02 P2 15
C2 01-FEB-03 P2 16
C3 01-APR-02 P3 17
C4 01-APR-03 P1 20
C4 01-JAN-06 P2 2
C4 05-AUG-07 P5 1
C1 01-MAY-06 P1 7
C1 01-MAY-07 P1 3
C5 01-AUG-07 P4 7
C5 02-AUG-08 P1 5

In the above example, my results should be

C1
C2
C3
Re: Avoiding 2 Group By clauses [message #660828 is a reply to message #660826] Mon, 27 February 2017 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member
>I want the faster method of returning the customers who purchased one and only one product id.
Faster than what?
Re: Avoiding 2 Group By clauses [message #660832 is a reply to message #660828] Mon, 27 February 2017 09:39 Go to previous messageGo to next message
manikandan23
Messages: 25
Registered: February 2017
Junior Member
select customer_id, count(*) from
(
select distinct customer_id, product_id from orders
)
group by customer_id having count(*) = 1

Faster than above?

I said 2 Group BYs because distinct is eventually a group by on this case.
Re: Avoiding 2 Group By clauses [message #660833 is a reply to message #660832] Mon, 27 February 2017 10:02 Go to previous messageGo to next message
Stew Ashton
Messages: 4
Registered: February 2017
Junior Member
How about this?
select cust_id from tbl
group by cust_id
having count(distinct prod_id) = 1;
Re: Avoiding 2 Group By clauses [message #660834 is a reply to message #660833] Mon, 27 February 2017 10:17 Go to previous messageGo to next message
manikandan23
Messages: 25
Registered: February 2017
Junior Member
Looks perfect Thanks!
Re: Avoiding 2 Group By clauses [message #660840 is a reply to message #660826] Mon, 27 February 2017 12:00 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 27 February 2017 16:29
Michel Cadot wrote on Sun, 26 February 2017 08:37

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Previous Topic: can we use column alias in having clause
Next Topic: 2 column values going inverse and how to display corresponding keys
Goto Forum:
  


Current Time: Fri Jan 19 18:26:39 CST 2018

Total time taken to generate the page: 0.01103 seconds