Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Question Unanswered: convert ms sql query to mysql

    hi

    1 have a table with this structure and data
    HTML Code:
    ID	UserID	IP
    
    ----------------------------------
    
    1	1	127.0.0.1
    2	1	127.0.0.1
    3	1	127.0.0.1
    4	1	127.0.0.1
    5	1	127.0.0.1
    6	1	127.0.0.1
    7	1	4.2.2.2
    8	1	4.2.2.2
    9	1	4.2.2.2
    10	1	4.2.2.2
    11	1	4.2.2.2
    12	1	192.168.1.1
    1 wrote this query in the Microsoft sql server :
    Code:
    select 
    
    	distinct UserID ,IP ,
    	
    	ROUND(COUNT(IP)  / cast((select COUNT(userid) from Users) as float) , 2 ) as '%' 
    
    	from Users
    	
    	group by UserID ,  ip ;

    and results is :
    HTML Code:
    UserID      IP                   %
    ----------- -------------------- ----------------------
    1           127.0.0.1            0.53
    1           192.168.0.1          0.42
    1           4.2.2.2              0.05
    
    (3 row(s) affected)
    i can`t convert it to mysql query

    please help me

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT UserID 
         , IP
         , ROUND( 1.00 * COUNT(IP) / 
             (SELECT COUNT(userid) FROM Users)  
                , 2 ) AS '%' 
    	FROM Users
    GROUP 
        BY UserID
         , IP
    mysql does not support "cast as float" -- but then, you shouldn't need to do that in sql server, either

    also, you don't need the DISTINCT in either mysql or sql server, because the groups produced by the GROUP BY clause are -- by definition -- distinct already
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    2
    thank u for ur attention

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •