help with mysql query - Printable Version

help with mysql query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: help with mysql query (/showthread.php?tid=67047)

help with mysql query - gosatriani - 01-07-2017

Hi All,

i have a table in mysql with the following data

Code:
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

Code:
      
Assettype              In Stock    Allocated    Decommisioned       Location  

Mouse                   1            1             0                chennai          

Keyboard                1            1             0                bangalore

Mouse                   0            0             1                hyderabad

Kindly help

RE: help with mysql query - badger - 01-08-2017

you need a pivot like
SELECT t.asset, SUM(IF(t.`status`="In Stock",1,0)) AS "In_Stock", SUM(IF(t.`status`="Allocated",1,0)) AS "Allocated", SUM(IF(t.`status`="Decommissioned",1,0)) AS "Decommissioned",
t.location
FROM tester t
GROUP BY t.asset, t.location
Bill