How to calculate MAX(SUM()) in postgresql in particular to query below

Question

I am trying to get MAX(SUM()) in PostgreSQL, which is not possible directly in PostgreSQL. I am trying to get the result by any method, but unable to get the desired result. My table is like this -

CREATE TABLE user_transactions (
    id integer NOT NULL,
    user_id bigint,
    credit_rupee numeric,
    created_at timestamp without time zone,
);

This is sample data

id      credit_rupee user_id    date 
1       30           72873      2015-03-25
2       10           76296      2015-03-25 
3       3            73130      2015-03-26 
4       2            73142      2015-03-26 
5       8.44         73145      2015-03-26
6       2            72911      2015-03-26 
7       5            73158      2015-03-26
8       10           73067      2015-03-26
9       2            76296      2015-03-26
10      1            76296      2015-03-27
11      5.5          73042      2015-03-27
12      2            73042      2015-03-27

In this i want to get the user with highest sum of credit_rupee for a particular day.

The result i want is

user_id  max date
72873    30  2015-03-25
73067    10  2015-03-26
73042    5.5 2015-03-27

The problem is i cannot select all three user_id,max(),date without GROUP BY date and user_id which makes it difficult to arrive at the result mentioned above.

i have tried many combinations like

SELECT MAX(sum),user_id,date FROM(
    SELECT SUM(credit_rupee),user_id,DATE(created_at) FROM user_transactions 
    GROUP BY user_id,DATE(created_at) 
    ORDER BY DATE(created_at)) as m 
GROUP BY date ORDER BY date;

SELECT DISTINCT(user_id),max,date from(
    SELECT MAX(sum),user_id,date FROM(
        SELECT SUM(credit_rupee),user_id,DATE(created_at) FROM user_transactions
        GROUP BY user_id,DATE(created_at) ORDER BY DATE(created_at)) as m 
    GROUP BY DATE,user_id ORDER BY date) as x 
GROUP BY date,user_id

Etc., But no matter however tried unable to get the result which i wanted. Can anybody help me how to arrive at the result? Thanks

mantigatos · Accepted Answer · 2015-04-30 14:53:45Z

1

This query will give you exactly what you want:

SELECT user_id,
       credit_rupee,
       created_at from
  (SELECT
     rank() OVER (PARTITION BY created_at ORDER BY credit_rupee DESC) AS rank,
     user_id, credit_rupee, created_at
     FROM user_transactions ) sq
WHERE rank=1;

answered Apr 30, 2015 at 14:53

mantigatos

2961 silver badge6 bronze badges

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

Comments

Clodoaldo Neto · Accepted Answer · 2015-04-30 13:46:46Z

0

Aggregate in a subquery and use distinct on in the outer:

select distinct on (1, 2)
    date, user_id, credit_ruppe
from (
    select user_id, date, sum(credit_ruppe) as credit_ruppe
    from sample s
    group by 1, 2
) s
order by date, user_id, credit_ruppe desc

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

answered Apr 30, 2015 at 13:46

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

2 Comments

vrOom Over a year ago

Clodoaldo Neto, I tried your query like this

SELECT DISTINCT ON (1,2) date,user_id,credit_rupee from(     SELECT user_id,date,MAX(sum) as credit_rupee FROM(         SELECT SUM(credit_rupee),user_id,DATE(created_at) FROM user_transactions WHERE status =3         GROUP BY user_id,DATE(created_at) ORDER BY DATE(created_at)) as m      GROUP BY 1,2 ) as x  order by date,user_id,credit_rupee desc

but its similar to what i ve tried before which i didn't mentioned here in the question

vrOom Over a year ago

Like this

SELECT date,user_id,MAX from( 	SELECT MAX(sum),user_id,date FROM( 		SELECT SUM(credit_rupee),user_id,DATE(created_at) FROM user_transactions  		GROUP BY user_id,DATE(created_at) ORDER BY DATE(created_at)) as m  	GROUP BY DATE,user_id ORDER BY date) as x

which doesnt fetch the result as required

Collectives™ on Stack Overflow

How to calculate MAX(SUM()) in postgresql in particular to query below

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related