CODEWITHSUNDEEP
sqloracle-databasegroup-by
Rodi Marcus
Rodi Marcus

Reputation: 107

Oracle display customer who purchased most cars

I'm currently trying to answer the following question:

Display the name of the customer who has purchased the most cars from Archie’s Luxury Motors.

Tables I'm working with:

Customer 

(custID, name, DOB, streetAddress, suburb, postcode,
gender, phoneNo, email, type)

SalesTransaction 

(VIN, custID, agentID, dateOfSale, agreedPrice)

I have tried the following query:

select customer.name
from customer, salestransaction
where customer.custid = salestransaction.custid
group by (salestransaction.custid)
having count(salestransaction.custid) = max(salestransaction.custid);

I receive the following error:

ORA-00979: not a GROUP BY expression

Please tell me what I'm doing wrong.

Upvotes: 2

Views: 555

Answers (4)

Mohammad Ashfaq
Mohammad Ashfaq

Reputation: 1375

Probably this should work:

select * from (
select customer.name
from customer, salestransaction
where customer.custID = salestransaction.custID
group by (salestransaction.custID), customer.name
order by count(*) desc
) where rownum=1

Upvotes: 1

Matt
Matt

Reputation: 15061

Join them using joins rather than in the where clause, also put the 

SELECT c.name, MAX(s.custid) FROM (
SELECT c.name, Count(s.custid)
FROM customer c
INNER JOIN salestransaction s ON c.custid = s.custid
GROUP BY c.name);

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

Easiest way utilizes a RANK:

select customer.name, st.cnt
from customer
join
 ( 
   select 
      custid,
      count(*) as cnt,
      rank() over (order by count(*) desc) as rnk
   from salestransaction
   group by custid
 ) st
on customer.custid = st.custid 
where st.rnk = 1;

Upvotes: 2

Sameer
Sameer

Reputation: 4389

select * from (
select customer.name, count(*)
from customer, salestransaction
where customer.custid = salestransaction.custid
group by (salestransaction.custid)
order by count(*) desc
) where rownum=1

Upvotes: 1

Related Questions