Split mysql column in codeigniter

Question

I have a table, called mess_stock. mess_stock table has following values,

sno    pay_amount   pay_type
1       10           1
2       60           1
2       500          2

I want to sum the payment by its payment type and wants to rename it as mess_pay if pay_type is 1, est_pay if pay_type is 2.

I have tried the following query

select pay_amount,sum(if(pay_type=1, pay_amount, 0)) mess_pay, sum(if(pay_type=2, pay_amount, 0)) est_pay from mess_stock group by pay_type;

this will gives as the following value

+------------+----------+---------+
| pay_amount | mess_pay | est_pay |
+------------+----------+---------+
|      10.00 |    70.00 |    0.00 |
|     500.00 |     0.00 |  500.00 |
+------------+----------+---------+

But I need answer like this

+----------+---------+
| mess_pay | est_pay |
+----------+---------+
|    70.00 |  500.00 |
+----------+---------+

What can I do?

Ankit Bajpai · Accepted Answer · 2015-02-23 09:54:26Z

1

Try this:-

SELECT SUM(IF(pay_type = 1, pay_amount, 0)) mess_pay, SUM(IF(pay_type=2, pay_amount, 0)) est_pay 
FROM mess_stock;

I hope this can help you.

edited Feb 23, 2015 at 9:54

answered Feb 23, 2015 at 8:17

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Praveen Srinivasan Over a year ago

it said ERROR 1111 (HY000): Invalid use of group function

Praveen Srinivasan Over a year ago

this works fine SELECT SUM(IF(pay_type = 1, pay_amount, 0)) mess_pay, SUM(IF(pay_type=2, pay_amount, 0)) est_pay FROM mess_stock;

Collectives™ on Stack Overflow

Split mysql column in codeigniter

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related