How to query MAX(SUM(relation)) in Postgresql?

Question

I have read several related threads on StackOverflow but none of them solves my problem. I have a Sales database as where I need to query for the customer who spent the most amount in buying stuff. For that, I need to find who bought which product using

SELECT  sum(qty*rate) 
AS exp from salesdetails as s JOIN sales as ss on (ss.invno = s.invno) 
JOIN customer as c ON (ss.customerno = c.custno) GROUP BY(c.name) 
ORDER BY sum(qty*rate);

It returns a table with the name of the person and what he spent in ascending order as

Output of command above:

While what I actually need is to only print a tuple when sum(qty*rate) is maximum. Currently I'm getting the results by sub querying like:

SELECT name, sum(qty*rate) FROM salesdetails as s JOIN sales as ss on (ss.invno=s.invno) 
JOIN customer as c ON (ss.customerno = c.custno) GROUP BY(c.name) 
HAVING sum(qty*rate) IN (SELECT max(exp) FROM (SELECT  sum(qty*rate) 
AS exp from salesdetails as s JOIN sales as ss on (ss.invno = s.invno) 
JOIN customer as c ON (ss.customerno = c.custno) GROUP BY(c.name) ORDER BY sum(qty*rate)) aa);

Expected Output:

Is there any shorter way to get to the output?

Eray Balkanli · Accepted Answer · 2018-02-08 17:49:57Z

1

Are you looking for something like this:

select * 
from (
       SELECT c.Name, sum(qty*rate) 
       AS exp from salesdetails as s JOIN sales as ss on (ss.invno = s.invno) 
       JOIN customer as c ON (ss.customerno = c.custno) 
       GROUP BY(c.name) 
       ORDER BY sum(qty*rate) desc
     ) t
limit 1;

edited Feb 8, 2018 at 17:49

answered Feb 8, 2018 at 17:24

Eray Balkanli

8,05013 gold badges60 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Aashutosh Rathi Over a year ago

It gave the following error: ERROR: syntax error at or near "1" LINE 1: select top 1 * ^

Eray Balkanli Over a year ago

Edited the answer. Could u please try again? @AashutoshRathi

Aashutosh Rathi Over a year ago

It gives ERROR: syntax error at or near ";" LINE 6: ORDER BY sum(qty*rate) desc; now. also, you have to add a semicolon at end.

Eray Balkanli Over a year ago

Maybe now, removed the semi-colon after desc and added it to the end? @AashutoshRathi

Eray Balkanli Over a year ago

The columns you want to see in your output, you can add them in the select clause of the subquery. Edited answer. @AashutoshRathi

|

Gordon Linoff · Accepted Answer · 2018-02-08 17:23:59Z

1

You want row_number() or distinct on:

SELECT DISTINCT ON (c.name) c.name, sum(qty*rate) AS exp
FROM salesdetails s JOIN
     sales ss
     on (ss.invno = s.invno) JOIN
     customer c
     ON (ss.customerno = c.custno)
GROUP BY c.name
ORDER BY c.name, sum(qty*rate) DESC;

answered Feb 8, 2018 at 17:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Aashutosh Rathi Over a year ago

It gives the whole table, I just need the one who spent most.

Collectives™ on Stack Overflow

How to query MAX(SUM(relation)) in Postgresql?

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related