Welcome Guest, Not a member yet? Register   Sign In
help with mysql query
#1

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
Reply
#2

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
Reply




Theme © iAndrew 2016 - Forum software by © MyBB