Unix/Linux Go Back    


Programming Post questions about C, C++, Java, SQL, and other programming languages here.

Need help in mysql query

Programming


Tags
mysql query, solved

Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 01-07-2017   -   Original Discussion by venkitesh
venkitesh venkitesh is offline
Registered User
 
Join Date: Dec 2016
Last Activity: 21 April 2017, 1:01 PM EDT
Posts: 27
Thanks: 6
Thanked 0 Times in 0 Posts
Need help in mysql query

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
Sponsored Links
    #2  
Old Unix and Linux 01-07-2017   -   Original Discussion by venkitesh
stomp stomp is offline
Registered User
 
Join Date: May 2016
Last Activity: 17 November 2017, 11:39 AM EST
Posts: 193
Thanks: 10
Thanked 67 Times in 62 Posts
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?
Sponsored Links
    #3  
Old Unix and Linux 01-07-2017   -   Original Discussion by venkitesh
venkitesh venkitesh is offline
Registered User
 
Join Date: Dec 2016
Last Activity: 21 April 2017, 1:01 PM EDT
Posts: 27
Thanks: 6
Thanked 0 Times in 0 Posts
Hi,

Got the answer finally.


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

Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
mysql query help mogabr Programming 1 01-11-2012 01:41 PM
mysql query help mogabr Web Programming 6 04-07-2011 10:40 AM
How to query one to many mysql pinkgladiator Programming 0 10-30-2008 02:45 PM



All times are GMT -4. The time now is 06:40 AM.