display data from the database (summarizes data)

please help me to get this enlightenment.
I want to display data from the database to be like this (summarizes data from the database).

this is just an example and ignore total penjualan and pendapatan.

from this table


thanks before

@DELE,

I'm unable to see the images. Can you try to fix them?

@DELE,

I'm unable to see the images. Can you try to fix them?

I fixed it

@DELE,

I need to understand what the third column is suppose to be. I get the first column. Ignore the second column. What is the third column? is it a summary of a specific user? Do you want a fourth and fifth column? if so what values? Have you come up with a basic query?

Example: See the table 'income_user' => '00002' the result I want is like this.

ID               NAMA             INCOME/COUNTRY           TOTAL SALES             TOTAL INCOME
00001
00002          -                       4US, 1PH                           5                                   $ 53.00
00003

Information :
INCOME/COUNTRY = the result of merging 'income_country' based on 'income_user' and summed and separated by country.
TOTAL SALES = total sales from all countries (4US + 1PH = 5).
TOTAL INCOME = the result of adding 'income_value' based on 'income_user'.

for query problems I don't understand what to do. my mind is still blank.
my query is still like this:
 SELECT * FROM incomes

You need to find another free image hosting service, that one is erring all over the net.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

You need to find another free image hosting service, that one is erring all over the net.

picture is available

I think the table structure is making this very complex.
If you store the income values, you'd better save every entry for every country separately.

E.g.
ID, income_user, income_country, income_count, income_amout.

User '00003' would get 4 records in stead of one:
00001, US, 1, 10
00001, PH, 3, 30
00001, CN, 2, 20
00001, TH, 9, 90

This would make it much easier to count the total counts and amounts per user and per country, and display them in a summary.

thank you for those who have tried to help me.


I have got what I want.

by using a query like this:

Code:

$this->db->query("
    SELECT income_user, SUM(income_value) AS income_value, GROUP_CONCAT(income_country SEPARATOR ',') AS income_country
    FROM incomes
    GROUP BY income_user
")->result();

results :

Code:

Array
(
   [0] => stdClass Object
       (
           [income_user] => 00001
           [income_value] => 1962.00
           [income_country] => 1US,2US,3US,3US,3US,3US,3US,3US,3US,3US,1US,1PH,1US,1US,1ID,1IN,1IN,1US,1US
       )

   [1] => stdClass Object
       (
           [income_user] => 00002
           [income_value] => 53.00
           [income_country] => 2US,1US,1US,1PH
       )

   [2] => stdClass Object
       (
           [income_user] => 00003
           [income_value] => 150.00
           [income_country] => 1US, 3PH, 2CN, 9TH
       )

)

the question:

how do I sum income_country per-country?


the method I just created is like this:

Code:

<tbody>
   <?php foreach ($data['users_incomes'] as $row): ?>
       <tr>
           <td>
               <?php echo $row->income_user ?>
           </td>
           <td>
           </td>
           <td>
               <?php foreach (explode(',', $row->income_country) as $test): ?>
                   <?php echo $test ?> // HERE
               <?php endforeach ?>
           </td>
           <td>
               <?php echo array_sum(explode(',', $row->income_country)) ?>
           </td>
           <td>
               <?php echo '$ '.$row->income_value ?>
           </td>
       </tr>
   <?php endforeach ?>
</tbody>

@DELE,

Getting income per country becomes a little trickier because you have multiple countries per record. Does each country get the whole income_value or is it divided between the countries?

demo of data above [ http://sqlfiddle.com/#!9/a8074e/1 ]