• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.