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

    Unanswered: Logical problem with my Query

    Hi all,

    I have a database structure of :

    Code:
    CREATE TABLE /*!32312 IF NOT EXISTS*/ `bilgiler` (
      `kayitID` int(11) NOT NULL AUTO_INCREMENT,
      `hak_sahibi_id` bigint(15) DEFAULT NULL,
      `cinsiyet` tinyint(1) DEFAULT NULL,
      `dogumtarihi` date DEFAULT NULL,
      `islemtarihi` date DEFAULT NULL,
      `tanikodu` varchar(10) DEFAULT NULL,
      `tedavi_ili` tinyint(3) DEFAULT NULL,
      `dogum_ili` tinyint(3) DEFAULT NULL,
      `ikamet_ili` tinyint(3) DEFAULT NULL,
      PRIMARY KEY (`kayitID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin5;
    I created an ASP loop for i = 0 to 65 executing the following query of
    Code:
    Select count(distinct hak_sahibi_id) as hebele from bilgiler where year(islemtarihi)-year(dogumtarihi) ='" & i & "' and (islemtarihi between '2007-07-01' and '2008-06-30')
    So it must count the records with distinct of hak_sahibi_id.

    When I take the sum of the query results the number I get is nearly two times larger than the result of general query which is

    Code:
    Select count(distinct hak_sahibi_id) from bilgiler
    General query has a result of : 115.640
    And the sum of the loop results is : 217.752

    Any idea what the problem is? (Problem does not seem like ASP loop problem as I get the same results if I type the queries manually).

    Please help
    telmessos

  2. #2
    Join Date
    Mar 2006
    Posts
    56
    Hi,

    I don't read Turkish so I cannot understand what you are trying to do by looking at the table definition. Still, your result 115.640 vs. 217.752 does not seem to be a problem if hak_sahibi_id is NOT in one-to-one relationship with kayitID, the primary key.

  3. #3
    Join Date
    Jan 2010
    Posts
    26
    thanks for the reply. I took the sum of the sub queries which all have distinct counts and targeting different ages (this means none of the results must intercept) and the sum is higher than the total distinct count. That's very weird. There must be sth wrong with my SQL query logic and somehow the numbers must be intercepted.

    It is really funny.
    I sent three queries following each other. First one is for two years period and got a record number of 115.379

    Code:
    Select count(distinct hak_sahibi_id) from bilgiler where islemtarihi >= '2007-07-01' and islemtarihi <= '2009-06-30'
    and divided into two different years and got the following results:

    Count of 66.884 for the first year with the query of :

    Code:
    Select count(distinct hak_sahibi_id) from bilgiler where islemtarihi >= '2007-07-01' and islemtarihi <= '2008-06-30'
    And count of 91.802 with the query of :

    Code:
    Select count(distinct hak_sahibi_id) from bilgiler where islemtarihi >= '2008-07-01' and islemtarihi <= '2009-06-30'
    And the sum of 2 "1 year period" queries is 158.686.


    I think for some reason the records overlap on my query. I don't know what it is.
    Last edited by telmessos; 05-04-10 at 10:52.

  4. #4
    Join Date
    Mar 2006
    Posts
    56
    Is it possible that the same hak_sahibi_id is recorded against multiple kayitID values?

  5. #5
    Join Date
    Jan 2010
    Posts
    26
    kayitID is the unique key. there are multiple hak_sahibi_ids which I am getting rid of by using distinct.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to amuse me, please run:
    Code:
    SELECT count(distinct hak_sahibi_id) as hebele
    ,  Year(islemtarihi)- Year(dogumtarihi) AS year_difference
       FROM  bilgiler 
       WHERE (islemtarihi BETWEEN '2007-07-01' AND '2008-06-30')
       GROUP BY Year(islemtarihi)- Year(dogumtarihi)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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