Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: How to write the repeat of the users with same ID

    Hi All,

    I searched for the answer of my question on internet but could not find a solution. On my MySQL table, there some records consists of memberIDs.

    Exampls:


    Code:
     
    memberID  code     yil 
    134450      D23    2007 
    145798      F56    2008 
    687684      R45    2007 
    134450      D23    2007
    What I would like to do is, to make MySQL bring the results of the table with another column showing how many times a memberID appears in the same year. For the above example the result must be like below:


    Code:
    memberID  code     yil    repeat 
    134450     D23     2007    2 
    145798     F56     2008    1 
    687684     R45     2007    1 
    134450     D23     2007    2
    As you see memberID 134450 has 2 records in the year of 2007 for this reason repeat is 2.

    I will be glad if anyone can help.

    Thanks
    telmessos

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
     SELECT memberID,code,yil,COUNT(*) AS repeat
      FROM daTable
    GROUP BY memberID,code,yil
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    26
    I got the following error with the structure you gave to me...


    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'repeat from bilgiler where tanikodu='O80' group by hak_sahibi_id,dogumtarihi,isl' at
    line 1

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by telmessos
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'repeat from bilgiler where tanikodu='O80' group by hak_sahibi_id,dogumtarihi,isl' at line 1
    I didn't notice that code in Rudy's SQL so perhaps it might help if you provide all the SQL that your actually typing in and the real table definition.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, it appears that REPEAT is a reserved word

    try assigning a different alias to the COUNT(*) column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Oddly I thought that at first and checked on the MySQL reserved words site but I didn't see REPEAT. I just went back onto the site and found another little table of extra reserved words under the main table of reserved words

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite View Post
    Oddly I thought that at first and checked on the MySQL reserved words site but I didn't see REPEAT.
    take another look, it's right there between RENAME and REPLACE

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r937
    it's right there between RENAME and REPLACE
    It's never easy finding words on a page when they follow such ridiculously strict alphabetising rules, I much prefer a softer (more fuzzy) scheme where you can just "happen" upon the word - there is real pride in that!

  9. #9
    Join Date
    Jan 2010
    Posts
    26
    Thanks for the replies. I am sending the complete database structure.

    Code:
    +---------------+---------------------+------+-----+---------+----------------+
    | Field         | Type                | Null | Key | Default | Extra          |
    +---------------+---------------------+------+-----+---------+----------------+
    | kayitID       | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
    | hak_sahibi_id | int(15)         | YES  |     | NULL    |                |
    | cinsiyet      | varchar(1)          | YES  |     | NULL    |                |
    | dogumtarihi   | date                | YES  |     | NULL    |                |
    | islemtarihi   | date                | YES  |     | NULL    |                |
    | tanikodu      | varchar(10)         | YES  |     | NULL    |                |
    | taniadi       | varchar(255)        | YES  |     | NULL    |                |
    | tedavi_ili    | tinyint(2)          | YES  |     | NULL    |                |
    | dogum_ili     | varchar(50)         | YES  |     | NULL    |                |
    | ikamet_ili    | varchar(50)         | YES  |     | NULL    |                |
    +---------------+---------------------+------+-----+---------+----------------+

    what I exactly need to do is :

    add a column to the right of the table (not really just on the query result) which shows how many times a client (with hak_sahibi_id) has a record in a year (which is the year section of field islemtarihi. normal format is yyyy-mm-dd at the moment), and a where clause at the end like "where tanikodu='O35'"

    It is a complicated SQL query that's why I needed some help.

    Regards
    telmessos
    Last edited by telmessos; 01-11-10 at 12:13.

  10. #10
    Join Date
    Jan 2010
    Posts
    26
    I made the code working with group by. But I had a problem

    My SQL query is this:

    Code:
    SELECT bilgiler.*,COUNT(*) AS 'repeat' FROM bilgiler where tanikodu='M84.11' GROUP BY hak_sahibi_id,year(islemtarihi)
    This query writes the count of how many times a year each hak_sahibi_id recorded. But it doesn't show the actual records.

    Let's say a person with hak_sahibi_id of 123456 has three records in the year of 2007 I want my query show all three results with value 3 shown in the count section.

    For the moment it shows only one record with the value of 3 shown in the count section.

    I hope I could explain the situation.

    Regards

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT bilgiler.*
         , b.repeat
      FROM bilgiler 
    INNER
      JOIN ( SELECT hak_sahibi_id
                  , year(islemtarihi) AS yyyy
                  , COUNT(*) AS 'repeat' 
               FROM bilgiler 
              WHERE tanikodu='M84.11' 
             GROUP 
                 BY hak_sahibi_id
                  , year(islemtarihi) ) AS b
        ON b.hak_sahibi_id = bilgiler.hak_sahibi_id
       AND b.yyyy = year(bilgiler.islemtarihi)
     WHERE bilgiler.tanikodu='M84.11'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2010
    Posts
    26
    thanks mate it really helped

Posting Permissions

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