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 –
x
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
Advertisement
Answer
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;