nid help please

itanacious · #1 (**permalink**) 12-17-07, 13:03

hey guys can you help me with my query promblems, this is the table that i have created

----Accounts----
Date | amount | customerID

Date -> the current date and time of the date entry
Amount -> is the credit or debit / they differ when amount < 0 it is credit and when amount > 0 it's debit

Date is my primary key

sample data
Date | amount | customerID
12/13/07 | 100 | cus001
12/20/07 | 200 | cus001
12/23/07 | -50 | cus001
12/24/07 | 500 | cus005

I need to query like this: where customerID = cus001

Date | debit | credit | balance | customerID
12/13/07 | 100 | 0 | 100 | cus001
12/20/07 | 200 | 0 | 300 | cus001
12/23/07 | 0 | 50 | 250 | cus001

will it be possible?
thanks in advance...

r937 · #2 (**permalink**) 12-17-07, 13:09

Date is the primary key???

so, in other words, only one customer can make only one transaction on any date?

something seems very fishy about this

when is this assignment due?

Littlefoot · #3 (**permalink**) 12-17-07, 14:18

If we forget about design flaw, the answer is YES (it is possible) - it would require the DECODE (or CASE) and the SUM analytical function:

Code:

SQL> select * From accounts;

DATUM        AMOUNT CUSTOMERID
-------- ---------- ----------
13.12.07        100 cus001
20.12.07        200 cus001
23.12.07        -50 cus001
24.12.07        500 cus005

SQL> SELECT datum,
  2    CASE
  3      WHEN amount >= 0 THEN amount
  4      ELSE 0
  5     END debit,
  6    CASE
  7      WHEN amount > 0 THEN 0
  8      ELSE ABS(amount)
  9    END credit,
 10    SUM(amount) over (ORDER BY datum) balance,
 11    customerid
 12  FROM ACCOUNTS
 13  WHERE customerid = 'cus001';

DATUM         DEBIT     CREDIT    BALANCE CUSTOMERID
-------- ---------- ---------- ---------- ----------
13.12.07        100          0        100 cus001
20.12.07        200          0        300 cus001
23.12.07          0         50        250 cus001

SQL>

Next time, please, do some initial effort by yourself and come here if you have a question about the specific problem. This was one-time spoonfeeding; don't expect more of it.

itanacious · #4 (**permalink**) 12-17-07, 18:29

wow thx r937 ur right only one transaction per/day and about the smelly fishy ur right too. my teacher smell kinda fishy..

itanacious · #5 (**permalink**) 12-17-07, 20:00

Code:

SQL> select * From accounts;

DATUM        AMOUNT CUSTOMERID
-------- ---------- ----------
13.12.07        100 cus001
20.12.07        200 cus001
23.12.07        -50 cus001
24.12.07        500 cus005

SQL> SELECT datum,
  2    CASE
  3      WHEN amount >= 0 THEN amount
  4      ELSE 0
  5     END debit,
  6    CASE
  7      WHEN amount > 0 THEN 0
  8      ELSE ABS(amount)
  9    END credit,
 10    SUM(amount) over (ORDER BY datum) balance,
 11    customerid
 12  FROM ACCOUNTS
 13  WHERE customerid = 'cus001';

DATUM         DEBIT     CREDIT    BALANCE CUSTOMERID
-------- ---------- ---------- ---------- ----------
13.12.07        100          0        100 cus001
20.12.07        200          0        300 cus001
23.12.07          0         50        250 cus001

SQL>

thx in advance

r937 · #6 (**permalink**) 12-17-07, 20:24

translate? into what? english?

itanacious · #7 (**permalink**) 12-17-07, 20:58

can someone translate this Sql queries to Mysql
it use over() function which I have not idea what is the counterpart in MySql

Code:

SQL> select * From accounts;

DATUM        AMOUNT CUSTOMERID
-------- ---------- ----------
13.12.07        100 cus001
20.12.07        200 cus001
23.12.07        -50 cus001
24.12.07        500 cus005

SQL> SELECT datum,
  2    CASE
  3      WHEN amount >= 0 THEN amount
  4      ELSE 0
  5     END debit,
  6    CASE
  7      WHEN amount > 0 THEN 0
  8      ELSE ABS(amount)
  9    END credit,
 10    SUM(amount) over (ORDER BY datum) balance,
 11    customerid
 12  FROM ACCOUNTS
 13  WHERE customerid = 'cus001';

DATUM         DEBIT     CREDIT    BALANCE CUSTOMERID
-------- ---------- ---------- ---------- ----------
13.12.07        100          0        100 cus001
20.12.07        200          0        300 cus001
23.12.07          0         50        250 cus001

SQL>

thx in advance

itanacious · #8 (**permalink**) 12-17-07, 21:07

sorry I just want to know what is counterpart of over()function in MySql
I'm just confuse how the Over() function works

I'm so Embarrass, sorry.. I'm Just Learning ORA, although I have some background in MySql.

r937 · #9 (**permalink**) 12-17-07, 21:21

ah, into mysql

well, i am curious why you posted in the oracle forum

okay, moving your thread to the mysql forum

p.s. please stop creating new threads, it is just more work for us to merge them back into one

thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech