Logical problem with my Query

telmessos · #1 (**permalink**) 05-04-10, 08:37

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

Ikviens · #2 (**permalink**) 05-04-10, 09:37

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.

telmessos · #3 (**permalink**) 05-04-10, 09:49

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.

Ikviens · #4 (**permalink**) 05-04-10, 10:24

Is it possible that the same hak_sahibi_id is recorded against multiple kayitID values?

telmessos · #5 (**permalink**) 05-04-10, 10:48

kayitID is the unique key. there are multiple hak_sahibi_ids which I am getting rid of by using distinct.

Pat Phelan · #6 (**permalink**) 05-04-10, 13:42

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

Internet Services

Web Development

Digital Marketing

Consumer Tech