Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2013
    Location
    Jakarta, Indonesia
    Posts
    1

    Question Unanswered: MySQL query for total sales per month and payment type

    Hello, i have a problem to display the data table in different format. In common report format.
    I have table transaction in database like this :

    ----------------------------------
    trans_date | pay_type | total
    ----------------------------------

    In pay type i have enum value with this rules :
    0 = cash
    1 = debit
    2 = credit

    I want to display the data like this :
    --------------------------------------------------------
    trans_date | cash | debit | credit | total
    --------------------------------------------------------
    2013-04-10 | 1000 | 0 | 500 | 1500
    etc.


    I have query like this :
    Code:
    SELECT trans_date, 
        CASE WHEN pay_type= 0 THEN sum(total) ELSE 0 END AS Cash, 
        CASE WHEN pay_type= 1 THEN sum(total) ELSE 0 END AS Debit, 
        CASE WHEN pay_type= 2 THEN sum(total) ELSE 0 END AS Credit, 
        sum(total) AS Total
    FROM tb_detail_transjual
    GROUP BY trans_date
    but the data in cash,debit,credit columns just show in one column,
    example, i have in 2013-10-02 2 transaction 1000 as cash and 500 as credit.

    The data in my query above shown like this :
    --------------------------------------------------------
    trans_date | cash | debit | credit | total
    --------------------------------------------------------
    2013-04-10 | 0 | 0 | 1500 | 1500
    etc.

    Please help me, thank you
    Last edited by fanjavaid; 10-26-13 at 14:45. Reason: Add syntax highlight

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT trans_date
    ,  Sum(CASE WHEN pay_type= 0 THEN total ELSE 0 END) AS Cash
    ,  Sum(CASE WHEN pay_type= 1 THEN total ELSE 0 END) AS Debit 
    ,  Sum(CASE WHEN pay_type= 2 THEN total ELSE 0 END) AS Credit 
    ,  Sum(total) AS Total
       FROM tb_detail_transjual
       GROUP BY trans_date
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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