sweb
December 11, 2008, 5:58pm
1
i need the MySQL query for calculate the number the rows that have a same value in a one or more cells:
See my example:
-----------------------------------------# | user_ip | user_useragent | user_lang-----------------------------------------1 | 55.12.15 | Firefox | en2 | 55.12.11 | Firefox | en3 | 55.12.15 | IE | en4 | 55.12.15 | Opera | en5 | 55.12.15 | Firefox | de6 | 55.12.15 | IE | en7 | 55.12.81 | Firefox | en-----------------------------------------
Request : That calculate number of rows that have a same value in the user_lang
and user_useragent
:
The rows 1, 2, 7 have same value in user_lang
and user_useragent
. >> 3 row
Also the rows 3 and 6 have same value in user_lang
and user_useragent
. >> 2 row
The result must be 5.
Did you understand? I want the SQL comment to process for getting number 5.
I can use the array_unique in PHP for calculate but i want process it with MySQL functions.
thanks and be waiting.
januzi
December 11, 2008, 6:13pm
2
edit:
select sum(amount) from (
select user_agent, user_lang, count(*) as amount from table group by concat( user_agent, user_lang)
) as t1 where amount > 1
sweb
December 11, 2008, 6:23pm
3
thank for your quick replying.
it’s complicate.
please show the some tutorial link to lean the complicate sql query.
januzi
December 11, 2008, 6:30pm
4
[URL=“http: ;/ ;/ ;www.xaprb.com/blog/ ”]http://www.xaprb.com/blog/[/URL]
[URL=“http: ;/ ;/ ;www.mysqlperformanceblog.com/ ”]http://www.mysqlperformanceblog.com/[/URL]
Those two guys are the best. I looked for other sources of knowledge, but I couldn’t find anything interesting.
You could also google: mysql subquery, mysql subqueries. You should find syntax etc.
sweb
January 21, 2009, 4:26pm
5
i have this data:
user_agent | user_lang==========================Firefox | enIE | enIE | enFirefox | fi
and excute this values for my question:
select sum(amount) from (select user_agent, user_lang, count(*) as amount from table group by concat( user_agent, user_lang)) as t1 where amount > 1
returned value is 2 that i want to be 3.
we have 3 rows that have different value in user_agent and user_lang.
thank and be waiting for answer.
januzi
January 21, 2009, 5:58pm
6
In that case remove “where amount > 1”
You will get all records, even if there was only one visitor with that browser and language
sweb
January 21, 2009, 10:44pm
7
i execute this query:
select sum(amount) from (select user_agent, user_lang, count(*) as amount from table group by concat( user_agent, user_lang)) as t1
the result is 4. i want to have result 3. (