Results 1 to 9 of 9

Thread: nid help please

  1. #1
    Join Date
    Dec 2007
    Posts
    5

    Question Unanswered: nid help please

    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...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  4. #4
    Join Date
    Dec 2007
    Posts
    5
    wow thx r937 ur right only one transaction per/day and about the smelly fishy ur right too. my teacher smell kinda fishy..

  5. #5
    Join Date
    Dec 2007
    Posts
    5

    Lightbulb translate this SQL queries PLEASE.....

    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    translate? into what? english?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Posts
    5

    Smile Nid help Please

    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

  8. #8
    Join Date
    Dec 2007
    Posts
    5
    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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •