CodeIgniter Forums
Outputting Select Sum - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Outputting Select Sum (/showthread.php?tid=21954)

Outputting Select Sum - El Forum - 08-25-2009

[eluser]tecrik[/eluser]
Hey -

I am trying to output the sum of a database column. The calculations seem to work but the output is not a single number but an array which looks like this:

Array ( [SUM(summe)] => 592299 )

How do I get CI to only show '592299'? I pasted the relevant code below.

Thanks!


Model:
Code:
function revenue ()
    {
    $query = $this->db->select('SUM(summe)', FALSE)
    ->from('orders')
    ->where('hidden', 0)
    ->get();
                                            
    return $query->row_array();
    }

Controller:
Code:
...
$data['revenue'] = $this->m_bestellung->revenue();
        
    $content = $this->parser->parse('bestellungen/template', $data, true);
    $this->view_template($content, $data);
    }

View:
Code:
...
<?php
print_r ($revenue);
?>
..


Outputting Select Sum - El Forum - 08-25-2009

[eluser]Dam1an[/eluser]
The "other stuff" is just the key in the array
You could always alias it using 'AS' and then use that key in the array to get just the value

Code:
function revenue ()
    {
    $query = $this->db->select('SUM(summe) AS `total`', FALSE)
    ->from('orders')
    ->where('hidden', 0)
    ->get();
                                            
    $row = $query->row_array();
    return $row['total'];
    }

Obviously put a check to make sure it's set and what not before you convert query to row_array Wink

Outputting Select Sum - El Forum - 08-25-2009

[eluser]tecrik[/eluser]
Thanks, that worked! Knowing about "AS" would have helped a lot ;-) I find myself having to learn PHP and CI at the same time on a project that's just a bit to extensive for that purpose ;-)

I now know wrote the function like this:

Code:
function revenue ()
{
$query = $this->db->select('SUM(summe) AS `total`', FALSE)
    ->from('orders')
    ->where('hidden', 0)
    ->get()
    ->row();
                                            
    return $query->total;
            
        }


Outputting Select Sum - El Forum - 08-25-2009

[eluser]jedd[/eluser]
AS is a SQL construct.

If you're leaning PHP, CI and SQL all at the same time .. you'll find lots of surprises like this. Wink

Outputting Select Sum - El Forum - 08-25-2009

[eluser]tecrik[/eluser]
No kidding, I am having a blast Wink Thanks so much for the help here, though!

So I ran into a different problem with this part:

Code:
function order_value_average ()
{
  $this->db->select_avg('summe');
  $query = $this->db->get('orders');

  return $query->summe;
            
}

According to the user guide, this part

Code:
$this->db->select_avg('summe');
$query = $this->db->get('orders');

should produce something like "SELECT AVG(summe) as summe FROM members" which makes the entire snippit very similar to the one in my inital post. Still, I get the following error message "Undefined property: CI_DB_mysql_result::$summe" in my view.

In general, I am confused about the "return" part, meaning how to return data and in which way. Is there some kind of documentation for that?

Outputting Select Sum - El Forum - 08-25-2009

[eluser]Dam1an[/eluser]
you need the second paramter to give it an alias again
At the moment, it just averages the summe column

Outputting Select Sum - El Forum - 08-26-2009

[eluser]tecrik[/eluser]
What do you mean second parameter? I tried

Code:
$this->db->select_avg('summe', 'summe');

as well as

Code:
$this->db->select_avg('summe', $summe);

Thanks!

Outputting Select Sum - El Forum - 08-26-2009

[eluser]Dam1an[/eluser]
Taken from the user guide
Quote:Writes a "SELECT AVG(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

Code:
$this->db->select_max('age', 'member_age');
$query = $this->db->get('members');
// Produces: SELECT MAX(age) as member_age FROM members

So in the same way you aliased the SUM operation to total, you can do the same here, and then use that name to access the variable in your row

Outputting Select Sum - El Forum - 08-26-2009

[eluser]tecrik[/eluser]
Thanks, don't know why it didn't work but I am using it like this now:

Code:
function order_value_average ()
       {

    $this->db->where('hidden', 0);
    $this->db->select_avg('summe', 'average', FALSE);
    $query = $this->db->get('orders')
    ->row();
                                            
        return $query->average;
            
    }