Group By Problem

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

[eluser]Ed Robindon[/eluser]
How about:

select fkmember, sum(amount) from points_ledger group by fkmember

[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)

[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, fkmember

[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?

[eluser]rochellecanale[/eluser]
Ok it works.. Thanks for the help..

[eluser]rochellecanale[/eluser]