I faced this problem on SQLServer, so i though it might be some bug (because I could not find out why I am facing this problem), but, then I moved data to Oracle, but I see same problem here, which means there is some problem with my data somewhere or with the queries which I have.

I have a table D_CUSTOMER which contains data of my customers based on a membership card. So a single customer may have multiple rows here if he has multiple membership cards.

Then I have a FURNITURE table which contains transactions by the customers.

I execute following query to fetch the data from y FURNITURE table to find out the number of unique customers (removed duplication), number of visits done by these customers (distinct RECEIPTNO will return us this because each visit will have a distinct receipt number) and then total amount spent by all customers.

- Code: Select all
`select COUNT(DISTINCT IDNO) AS UNIQ, COUNT(distinct RECEIPTNO) AS VISIT, SUM(AMT_TXN) AS SPENT`

from FURNITURE

where trandate_period_skey between 946 and 1126

UNIQ VISIT SPENT

148269 265907 5551854.18

Now I want same information based on gender of the customers, so i used following query

- Code: Select all
`select GENDER, COUNT(DISTINCT A.IDNO) AS UNQI, COUNT(distinct RECEIPTNO) AS VISIT, SUM(AMT_TXN) AS SPEND`

from FURNITURE A,

(SELECT IDNO, MAX(GENDER) AS GENDER FROM D_CUSTOMER WHERE MEMBERTYPE = 'PA' GROUP BY IDNO) b

where TRANDATE_PERIOD_SKEY between 946 and 1126

and a.IDNO = b.IDNO

group by gender

GENDER UNIQ VISITS SPENT

1 14 19 448.61

F 101009 183629 3770651.08

M 43988 76702 1652281.4

P99 3055 5237 121099.35

~ 203 344 7373.74

FURNITURE table contains data only from members with MEMBERTYPE = 'PA'. Here we are using MAX(GENDER) because there are multiple entries for the same customer. So it is supposed to pick only one entry against a CUSTOMER using MAX function.

Now the problem is that if we sum the values under VISIT column, it should be 265907 as returned by the first query, but, after adding values for VISIT column, the actual value is 265931.

Can you please help me to understand that why I am having this difference?

Thanks

Salman