OraFAQ Forum: SQL & PL/SQL » Avoiding 2 Group By clauses

Home » SQL & PL/SQL » SQL & PL/SQL » Avoiding 2 Group By clauses

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Avoiding 2 Group By clauses [message #660826]

Mon, 27 February 2017 09:28

manikandan23
Messages: 34
Registered: February 2017

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

Report message to a moderator

Re: Avoiding 2 Group By clauses [message #660828 is a reply to message #660826]

Mon, 27 February 2017 09:30

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Avoiding 2 Group By clauses [message #660832 is a reply to message #660828]

Mon, 27 February 2017 09:39

manikandan23
Messages: 34
Registered: February 2017

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.

Report message to a moderator

Re: Avoiding 2 Group By clauses [message #660833 is a reply to message #660832]

Mon, 27 February 2017 10:02

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;

Report message to a moderator

Re: Avoiding 2 Group By clauses [message #660834 is a reply to message #660833]

Mon, 27 February 2017 10:17

manikandan23
Messages: 34
Registered: February 2017

Member

Looks perfect Thanks!

Report message to a moderator

Re: Avoiding 2 Group By clauses [message #660840 is a reply to message #660826]

Mon, 27 February 2017 12:00

Michel Cadot
Messages: 68641
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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:01:39 CDT 2024