Where is the error in SQL query. error in using the aggregate function

Question

I have to tables

TABLE MEDCOND

AGRDATEBEG  
            AGRISN  
                    PREMRUB 
2000-05-10    1      15
2000-05-11    1      20
2000-05-10    2      35
2001-05-10    3      55
2000-05-10    4      66
2003-05-17    1      10
2000-05-10    2      88
2000-05-19    1      99
2000-06-10    3      98

TABLE MEDREFUND

 AGRDATEBEG 
                AGRISN  
                        REFUNDSUMRUB    
    2000-05-10   1       2000
    2000-05-11   1       2000
    2000-05-10   2       500
    2001-05-10   3       1000
    2000-05-10   4       2000
    2003-05-17   1       40
    2000-05-10   2       500
    2000-05-19   1       700
    2000-06-10   3       800

I need to calculate the amount(REFUNDSUMRUB-PREMRUB) for each type (1, 2,3) for May 10, 2000

My SQL query and results

SELECT medcond.AGRISN, SUM(DISTINCT(REFUNDSUMRUB-PREMRUB)) as sum_ FROM medcond INNER JOIN medrefund ON medcond.AGRISN=medrefund.AGRISN WHERE medcond.AGRDATEBEG='2000-05-10' GROUP BY medcond.AGRISN;

RESULTS

AGRISN  sum_    
1      2695
2      877
4      1934

I don't understand where the number 2695 for type 1 came from, it must be 1985, the rest of the results are correct. How to fix it?

Answer 1

Your join matches all of the data from table MEDREFUND regardless of the date (check my fiddle)

To calculate the total for each type, use CTEs to get total per table, then perform the soustraction. :

WITH refund_sum as (
  select AGRISN, sum(REFUNDSUMRUB) as REFUNDSUMRUB
  from medrefund
  WHERE AGRDATEBEG='2000-05-10'
  group by AGRISN
),
prem_sum as (
  select AGRISN, sum(PREMRUB) as PREMRUB
  from medcond
  WHERE AGRDATEBEG='2000-05-10'
  group by AGRISN
)
select r.AGRISN, REFUNDSUMRUB - PREMRUB as sum_
from refund_sum r
inner join prem_sum m on m.AGRISN = r.AGRISN

Result :

AGRISN  SUM
1       1985
2       877
4       1934

Demo here