Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Calculate same values [Problem]

swebsweb EntrantCurrent User Role Beginner
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
</pre>


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.

Comments

  • januzijanuzi Advisor Inactive User Role Beginner
    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
  • swebsweb Entrant Current User Role Beginner
    thank for your quick replying.
    it's complicate.
    please show the some tutorial link to lean the complicate sql query. :D
  • januzijanuzi Advisor Inactive User Role Beginner
    http://www.xaprb.com/blog/
    http://www.mysqlperformanceblog.com/

    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.
  • swebsweb Entrant Current User Role Beginner
    i have this data:

    user_agent | user_lang==========================Firefox | enIE | enIE | enFirefox | fi</pre>

    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 </pre>

    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.
  • januzijanuzi Advisor Inactive User Role Beginner
    In that case remove "where amount > 1"
    You will get all records, even if there was only one visitor with that browser and language
  • swebsweb Entrant Current User Role Beginner
    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</pre>

    the result is 4. i want to have result 3. (
  • januzijanuzi Advisor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">

    select count(*) as amount from (
    select user_agent, user_lang from table group by concat( user_agent, user_lang)
    ) as t1

    </td></tr></table>

    How 'bout this ?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.