Question 298594 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to add a temporary coloum for sql query

i have a sql query for summary my table,

        $this->where('kriteria =', 'fakir');
        $this->where('zona !=', '0');
        $this->select('surveyor,zona,kriteria,area');
        $this->selectCount('surveyor', 'count');
        $this->selectSum('nilai', 'nilai');
        $this->groupBy('surveyor');
        $this->groupBy('kriteria');
        $this->groupBy('area');
        $this->orderBy('kriteria', 'ASC');
        $this->orderBy('zona', 'ASC');
        $this->orderBy('count', 'DESC');
surveyor zona kriteria area COUNT
RT06 RW07 1 FAKIR RUNGKUT 125
RT08 RW07 1 FAKIR RUNGKUT 78

and i need to take only 1 data from the query result but i cant use surveyor cause it contan space. i plan to add maybe like id for identify data that i need but i dont know how, i use codeigniter 4.4.3

I’ve tried call the id but it refer to only one single data from before query

Tags:

2

Answers


  1. 0

    To convert this into a complete SQL query, it would look something like this:

    SELECT surveyor, zona, kriteria, area, COUNT(surveyor) AS count, SUM(nilai) AS nilai
FROM your_table
WHERE kriteria = 'fakir' AND zona != '0'
GROUP BY surveyor, kriteria, area
ORDER BY kriteria ASC, zona ASC, count DESC;
    Login or Signup to reply.
  2. 0 
    this->where('kriteria', 'fakir');
$this->where('zona !=', '0');
$this->select('surveyor, zona, kriteria, area');
$this->selectCount('surveyor', 'count');
$this->selectSum('nilai', 'nilai');
$this->select("CONCAT(surveyor, '_', zona, '_', kriteria, '_', area) as custom_id", false);
$this->groupBy('surveyor');
$this->groupBy('kriteria');
$this->groupBy('area');
$this->orderBy('kriteria', 'ASC');
$this->orderBy('zona', 'ASC');
$this->orderBy('count', 'DESC');

$queryResult = $this->get()->getResult();

$selectedRow = null;

foreach ($queryResult as $row) {
    if (strpos($row->custom_id, 'desired_value') !== false) {
        $selectedRow = $row;
        break;
    }
}

This example uses the CONCAT function to concatenate the values of surveyor, zona, kriteria, and area with underscores to create a custom_id for each row. You can adjust the concatenation logic based on your requirements. In the loop, you can then use any logic to select the desired row based on this custom_id or other criteria.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search