CodeIgniter query to pivot and SUM() database table records

Question

I have a table in MySQL with the following data.

Table name Test

Assettype   Serial_No     Status            location    
Mouse        123456      In Stock           chennai
Mouse        98765       Allocated          chennai
Keyboard     23498       In Stock           bangalore
Keyboard     45646       Allocated          bangalore
Mouse        234234      Decommisioned      hyderabad

I am looking to execute a MySQL query in my Codeigniter application to provide the below output.

Assettype              In Stock    Allocated    Decommisioned       Location  
Mouse                   1            1             0                chennai          
Keyboard                1            1             0                bangalore
Mouse                   0            0             1                hyderabad

And what have you tried to do for yourself

RiggsFolly
– RiggsFolly

2017-01-07 16:01:50 +00:00
Commented Jan 7, 2017 at 16:01 — RiggsFolly
– RiggsFolly, Commented Jan 7, 2017 at 16:01
Looks like your initial database design need refactoring.

RiggsFolly
– RiggsFolly

2017-01-07 16:03:34 +00:00
Commented Jan 7, 2017 at 16:03 — RiggsFolly
– RiggsFolly, Commented Jan 7, 2017 at 16:03

Gurwinder Singh · Accepted Answer · 2017-01-07 16:19:54Z

1

You can do a group by on assettype and location and find the counts like this:

select 
  assettype,
  sum(status = 'In Stock') In_stock,
  sum(status = 'Allocated') Allocated,
  sum(status = 'Decommisioned') Decommisioned,
  location
from test
group by assettype, location;

answered Jan 7, 2017 at 16:19

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

catchvenki Over a year ago

hi Guru, when i put the same query in codeigniter its giving error from controller, call to underfine function $query.

Gurwinder Singh Over a year ago

I've no knowledge of php but I can assure you that this query doesn't have any issues. You should post the problem as a new question along with the error details.

mickmackusa · Accepted Answer · 2025-09-23 20:43:56Z

Your pivot query requires GROUP BY and SUM() calls.

Using array_reduce() is a clever way to iterate the statuses to be summed because it affords a single chain of method calls to build the query.

Notice that everything is quoted properly.

return array_reduce(
    ['In Stock', 'Allocated', 'Decommisioned'],
    fn($qb, $v) => $db->select_sum(
        $qb->escape_identifiers('Status') . '=' . $qb->escape($v),
        $v
    ),
    $this->db->select('Assettype,location')
)
->group_by(['Assettype', 'location'])
->order_by('Assettype, location')
->get('Test')
->result();

This will render:

SELECT
    `Assettype`,
    `location`,
    SUM(`Status` = 'In Stock') AS `In Stock`,
    SUM(`Status` = 'Allocated') AS `Allocated`,
    SUM(`Status` = 'Decommisioned') AS `Decommisioned`
FROM `Test`
GROUP BY `Assettype`, `location`
ORDER BY `Assettype`, `location`

Collectives™ on Stack Overflow

CodeIgniter query to pivot and SUM() database table records

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related