+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Group By Problem (/showthread.php?tid=55330)
Group By Problem - El Forum - 10-21-2012
[eluser]rochellecanale[/eluser]
hello guys i just want to ask a simple query in mysql. My problem is i want to have a group by in my statement but i cannot get the right values. Here's my query result code in mysql:
Code:
mysql> select * from points_ledger;
+-----------+---------------------+--------+--------+-----------------+----------+
| ledger_id | completion_date | type | amount | running_balance | fkmember |
+-----------+---------------------+--------+--------+-----------------+----------+
| 4 | 2012-10-16 06:21:09 | EARNED | 5 | 0.00 | 1 |
| 5 | 2012-10-16 06:28:25 | EARNED | 25 | 0.00 | 1 |
| 6 | 2012-10-03 04:41:53 | EARNED | 5 | 0.00 | 37 |
| 7 | 2012-10-05 03:27:28 | EARNED | 5 | 0.00 | 47 |
| 8 | 2012-10-05 05:57:45 | EARNED | 5 | 0.00 | 53 |
| 9 | 2012-10-05 06:06:37 | PAYOUT | -5 | 0.00 | 1 |
| 10 | 2012-10-05 06:06:37 | PAYOUT | -25 | 0.00 | 1 |
| 11 | 2012-10-05 06:06:37 | PAYOUT | -5 | 0.00 | 37 |
| 12 | 2012-10-05 06:06:37 | PAYOUT | -5 | 0.00 | 47 |
| 13 | 2012-10-05 06:06:37 | PAYOUT | -5 | 0.00 | 53 |
+-----------+---------------------+--------+--------+-----------------+----------+All i want is to group by the same fkmember and add the total amount per member. For example I want to have a result like this.
Code:
mysql> select * from points_ledger;
+-----------+---------------------+--------+--------+-----------------+----------+
| ledger_id | completion_date | type | amount | running_balance | fkmember |
+-----------+---------------------+--------+--------+-----------------+----------+
| 4 | 2012-10-16 06:21:09 | EARNED | 30 | 0.00 | 1 |
| 6 | 2012-10-03 04:41:53 | EARNED | 5 | 0.00 | 37 |
| 7 | 2012-10-05 03:27:28 | EARNED | 5 | 0.00 | 47 |
| 8 | 2012-10-05 05:57:45 | EARNED | 5 | 0.00 | 53 |
| 9 | 2012-10-05 06:06:37 | PAYOUT | -30| 0.00 | 1 |
| 11 | 2012-10-05 06:06:37 | PAYOUT | -5 | 0.00 | 37 |
| 12 | 2012-10-05 06:06:37 | PAYOUT | -5 | 0.00 | 47 |
| 13 | 2012-10-05 06:06:37 | PAYOUT | -5 | 0.00 | 53 |
+-----------+---------------------+--------+--------+-----------------+----------+The code above combine the result of fkmember and add the total amount (5+25)
How can i do this? I try using group by but it displays:
Code:
mysql> select * from points_ledger group by fkmember;
+-----------+---------------------+--------+--------+-----------------+----------+
| ledger_id | completion_date | type | amount | running_balance | fkmember |
+-----------+---------------------+--------+--------+-----------------+----------+
| 4 | 2012-10-16 06:21:09 | EARNED | 5 | 0.00 | 1 |
| 6 | 2012-10-03 04:41:53 | EARNED | 5 | 0.00 | 37 |
| 7 | 2012-10-05 03:27:28 | EARNED | 5 | 0.00 | 47 |
| 8 | 2012-10-05 05:57:45 | EARNED | 5 | 0.00 | 53 |
+-----------+---------------------+--------+--------+-----------------+----------+Please help me guys.
Group By Problem - El Forum - 10-21-2012
[eluser]Ed Robindon[/eluser]
How about:
select fkmember, sum(amount) from points_ledger group by fkmember
Group By Problem - El Forum - 10-21-2012
[eluser]rochellecanale[/eluser]
It returns to:
Code:
mysql> select fkmember, sum(amount) from points_ledger group by fkmember;
+----------+-------------+
| fkmember | sum(amount) |
+----------+-------------+
| 1 | 0 |
| 37 | 0 |
| 47 | 0 |
| 53 | 0 |
+----------+-------------+
4 rows in set (0.00 sec)Group By Problem - El Forum - 10-21-2012
[eluser]scottwire[/eluser]
I think this should work:
Code:
SELECT ledger_id, completion_date, type, sum(amount) as amount, running_balance, fkmember
FROM `points_ledger`
GROUP BY type, fkmemberGroup By Problem - El Forum - 10-21-2012
[eluser]rochellecanale[/eluser]
I have an idea but i dont know how to combine the two queries in one simple query, like a subquery statement.
Here's my idea. First i run this query.
Code:
//First for earned type
mysql> select sum(amount) as total,fkmember from points_ledger where type = 'EARNED' group by fkmember;
//It results to
+-------+----------+
| total | fkmember |
+-------+----------+
| 30 | 1 |
| 5 | 37 |
| 5 | 47 |
| 5 | 53 |
+-------+----------+
4 rows in set (0.00 sec)Code:
//Next is this for payout type
mysql> select sum(amount) as total,fkmember from points_ledger where type = 'PAYOUT' group by fkmember;
//results to
+-------+----------+
| total | fkmember |
+-------+----------+
| -30 | 1 |
| -5 | 37 |
| -5 | 47 |
| -5 | 53 |
+-------+----------+Now how can i combine the two queries?
Group By Problem - El Forum - 10-21-2012
[eluser]rochellecanale[/eluser]
Ok it works.. Thanks for the help..
Group By Problem - El Forum - 10-22-2012
[eluser]rochellecanale[/eluser]
