Web Analytics Made Easy -
StatCounter help me create a select statement - CodingForum

Announcement

Collapse
No announcement yet.

help me create a select statement

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • help me create a select statement

    Look at the following table

    ID NUMBER
    0 192.168.64.10
    0 192.168.64.10
    0 192.168.64.11
    4 192.168.64.11
    0 192.168.64.11
    4 192.168.64.10
    3 192.168.64.10
    3 192.168.64.10


    I want the select to produce the following results
    For each number return me the number of the same id's
    192.168.64.10
    0=>2 times appeared
    3=>2 times appeared
    4=>1 time appeared

    192.168.64.11
    0=>2 times appeared
    3=>0 times appeared //We can ignore this
    4=>1 times appeared

    Note: You might see some mistakes in the number of times each number appears.......

  • #2
    use a group by clause:

    SELECT count(*) as numrec, id, number FROM tablename GROUP BY id,number ORDER BY number ASC, id ASC


    you can then get the count like $row['numrec']
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

    Comment


    • #3
      group by

      Do u know if tis is possible to use the group by in a varchar field?

      Comment


      • #4
        Yes it is.
        Check
        http://www.mysql.com/doc/en/GROUP-BY-Functions.html
        for more info.
        Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

        Comment

        Working...
        X