How to write the repeat of the users with same ID

telmessos · #1 (**permalink**) 01-10-10, 15:58

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

r937 · #2 (**permalink**) 01-10-10, 16:48

Code:

 SELECT memberID,code,yil,COUNT(*) AS repeat
  FROM daTable
GROUP BY memberID,code,yil

telmessos · #3 (**permalink**) 01-11-10, 06:51

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

mike_bike_kite · #4 (**permalink**) 01-11-10, 07:45

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.

r937 · #5 (**permalink**) 01-11-10, 08:14

well, it appears that REPEAT is a reserved word

try assigning a different alias to the COUNT(*) column

mike_bike_kite · #6 (**permalink**) 01-11-10, 08:18

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

r937 · #7 (**permalink**) 01-11-10, 08:24

Quote:

Originally Posted by mike_bike_kite

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

mike_bike_kite · #8 (**permalink**) 01-11-10, 09:55

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!

telmessos · #9 (**permalink**) 01-11-10, 11:01

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

telmessos · #10 (**permalink**) 01-12-10, 09:53

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

r937 · #11 (**permalink**) 01-12-10, 10:03

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'

telmessos · #12 (**permalink**) 01-12-10, 10:09

thanks mate it really helped

Internet Services

Web Development

Digital Marketing

Consumer Tech