Need help in mysql query

Applications Programming
1

Hi All,

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

Kindly help

2

Well, you can do everything in a single complex select statement, but I suppose the execution time may be somewhat really slow.

How many records are in your table?

3

Hi,

Got the answer finally.

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