mysqlcodeigniteractiverecordpivotaggregate-functions

CodeIgniter active record query to pivot and SUM() database table records

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 for a MySQL query which will give the below mentioned output.

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

  • 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;