CODEWITHSUNDEEP
mysqldatabasereport
fanjavaid
fanjavaid

Reputation: 1738

MySQL query for total sales per month and payment type

Hello i have problem in Mysql query to display monthly report per payment type. I have database table like this.

uid | invoice_num | pay_type | trans_date | total

in pay_type i use enum type, where 0=cash, 1=debit and 2=credit.

I want to display the data like this.

Trans_date | Cash | Debit | Credit | Total
2013-10-01    1000      0          500     1500
etc.

This is my current query but, it return error.

SELECT 
(SELECT tgl_transaksi FROM tb_detail_transjual GROUP BY tgl_transaksi) as tanggal,

(SELECT SUM(total) FROM tb_detail_transjual
WHERE jenis_trans=0) as cash,

(SELECT SUM(total) FROM tb_detail_transjual
WHERE jenis_trans=1) as debit,

(SELECT SUM(total) FROM tb_detail_transjual
WHERE jenis_trans=2) as credit

Thank you.

Upvotes: 0

Views: 2021

Answers (1)

RustProof Labs
RustProof Labs

Reputation: 1277

You could use a CASE WHEN statement. I haven't tested this but it should work. You can add a ORDER BY trans_date to the end if you want to sort it.

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

Upvotes: 1

Related Questions