How Do I Summary 2 Column With Different Date And Group By

Question

0.00/5 (No votes)

See more:

here i got an example for the table
TABLE
=====
id_user | equity | schedule
123 | 2000 | 2015-09-29
124 | 2000 | 2015-09-29
125 | 2000 | 2015-09-29
126 | 2000 | 2015-09-29
127 | 2000 | 2015-09-29
123 | 3000 | 2015-09-28
124 | 3000 | 2015-09-28
125 | 1000 | 2015-09-28
126 | 1000 | 2015-09-28
127 | 2000 | 2015-09-28

what i want is something like this after the query,

id_user | summary
123 | -1000
124 | -1000
125 | 1000
126 | 1000
127 | 0

so the formula is the equity from date 29 - the equity from date 28
how can i do that ?
thanks. please teach me the query.

Posted 8-Oct-15 7:34am

Andrew Budiman

Add a Solution

Comments

CHill60 8-Oct-15 14:01pm

Are you using SQL or PostgreSQL and which version?

2 solutions

Solution 2

what if the scenario become like this,

id_user | equity | schedule
123 | 2000 | 2015-09-29
124 | 2000 | 2015-09-29
125 | 2000 | 2015-09-29
126 | 2000 | 2015-09-29
127 | 2000 | 2015-09-28
123 | 3000 | 2015-09-28
124 | 3000 | 2015-09-28
125 | 1000 | 2015-09-28
126 | 1000 | 2015-09-28
127 | 2000 | 2015-09-27

id_user | summary | schedule
123 | -1000 | 2015-09-29
124 | -1000 | 2015-09-29
125 | 1000 | 2015-09-29
126 | 1000 | 2015-09-29
127 | 0 | 2015-09-28

how can i do that ?

thanks.

Posted 9-Oct-15 3:29am

Andrew Budiman

Comments

CHill60 9-Oct-15 9:42am

If you want to add information to your question use the Improve question link. Don't post further questions as solutions.
I asked you 19 hours ago whether you were using SQL or PostgreSQL and which version - you need to be looking at previous rows not hard-coding dates, but I need to know which version in order to determine a solution

Andrew Budiman 9-Oct-15 11:45am

oops i'm sorry, its my bad, i'm using PostgreSQL version 9.4.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Accepted Answer · 2015-10-08T08:03:00

Solution 1

If date are fixed or you are going to pass these someway to the query then you can ctry this-

SQL

SELECT T1.id_user, T1.equity-T2.equity AS summary
FROM YourTable T1
LEFT JOIN YourTable T2 ON T1.id_user=T2.id_user
WHERE T1.schedule='2015-09-29' AND T2.schedule='2015-09-28'

If you want it to make dynamic, please let me know.

Hope, it helps :)

Posted 8-Oct-15 8:03am

Suvendu Shekhar Giri

Comments

Andrew Budiman 8-Oct-15 14:08pm

Thanks very much, as if your query is very helpful to me.

Andrew Budiman 8-Oct-15 14:23pm

Hi Suvendu,

what if the scenario become like this,

id_user | equity | schedule
123 | 2000 | 2015-09-29
124 | 2000 | 2015-09-29
125 | 2000 | 2015-09-29
126 | 2000 | 2015-09-29
127 | 2000 | 2015-09-28
123 | 3000 | 2015-09-28
124 | 3000 | 2015-09-28
125 | 1000 | 2015-09-28
126 | 1000 | 2015-09-28
127 | 2000 | 2015-09-27

id_user | summary | schedule
123 | -1000 | 2015-09-29
124 | -1000 | 2015-09-29
125 | 1000 | 2015-09-29
126 | 1000 | 2015-09-29
127 | 0 | 2015-09-28

how can i do that ?

thanks.