Sam Keith Sam Keith - 5 months ago 14
SQL Question

SQL query to find name with max price purchase made?

I have two tables,

customers
and
purchases
.
Purchases
has a
total_price
column and foreign key
cid
referencing
customers.cid
. I need to select names of customers (came) who made the highest total_price purchase.

I'm trying this

select
cname
from
customers c
where exists
(select pid
from purchases p
where total_price in (select max(total_price)
from purchases p
where max(total_price) = total_price
and p.cid = c.cid))


I get the error, group function is not allowed here -->

where max(total_price) = total_price


Please help me out

Answer

You could just sum up the totals for each customer and then order it by highest totals like so:

select cname, sum(total_price) as totals
from customers c
inner join purchases p
  on c.cid = p.cid
group by cname
order by totals desc
limit 1

Here's an example with MySQL database: http://sqlfiddle.com/#!9/133fa/1

I have an example with Oracle here: http://sqlfiddle.com/#!4/9d786/4

with 
totals as
(
  select cid, sum(total_price) as totals
  from purchases
  group by cid
), 
highest as (
  select max(totals) as highest from totals
)
select cname
from customers c
inner join totals t on c.cid = t.cid
inner join highest h on t.totals = h.highest
Comments