convert ms sql query to mysql

mehrdad_t · #1 (**permalink**) 02-24-10, 12:09

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

r937 · #2 (**permalink**) 02-24-10, 13:43

Code:

SELECT UserID 
     , IP
     , ROUND( 1.00 * COUNT(IP) / 
         (SELECT COUNT(userid) FROM Users)  
            , 2 ) AS '&#37;' 
	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

mehrdad_t · #3 (**permalink**) 02-26-10, 03:41

thank u for ur attention

Internet Services

Web Development

Digital Marketing

Consumer Tech