CODEWITHSUNDEEP
sql
user8973106
user8973106

Reputation: 1

SQL SUM repeats for every entry even with group by

I can't seem to figure out a simple SUM() scenario using group by.

I am trying to compare a billed total vs paid total from two different tables. What I currently have is:

SELECT  
  claimamount,
  SUM(billing_entries.paidamount)
FROM customers.billing_entries, customers.claim_items
WHERE
    lastclaimid = 2132206 
    AND billing_entries.lastclaimid = claim_items.claimid
GROUP BY  claimid, claimamount;

For this specific case the claimamount is 256.45 and the total paid is 244.44

When I run this I get the following:

256.4500 ; 977.7600

There are 4 entries in the claim and it is SUMs them each time and returns 4 * the actual sum of them 244.44.

Sample data for the two tables I pull them from:

claim_items: claimid = 2132206 claimamount = 256.45

billing_entries:

entryId(1) 27136421 paidamount(1) 25.64

entryId(2) 27136423 paidamount(2) 102.5800

entryId(3) 26803842 paidamount(3) 102.5800

entryId(4) 26803839 paidamount(4) 13.64

they all have the same lastclaimid of 2132206

Here are some screenshots of the data

claim_items

billing_entries

billingentrygroupid | claimamount | claimid | clientid | entryamount | entryquantity | entryunit | entryunitrate | id | linenumber | organizationid | procedurecode | receivedon | renderingprovidercontactid | renderingproviderid | renderingproviderotherid | senton
--------------------+-------------+---------+----------+-------------+---------------+-----------+---------------+----------+------------+----------------+---------------+---------------------+----------------------------+---------------------+--------------------------+-------------------- 24015783 | 256.45 | 2132206 | 174491 | 102.58 | 4.00 | UN | 51.29 | 12483241 | 2 | 168717 | 0365T | 2017-10-17 16:49:47 | 370211 | | 622843915 | 2017-10-06 18:58:57 24302220 | 256.45 | 2132206 | 174491 | 25.65 | 1.00 | UN | 51.29 | 12483242 | 3 | 168717 | 0364T | 2017-10-17 16:49:47 | 370211 | | 622843915 | 2017-10-06 18:58:57 24302220 | 256.45 | 2132206 | 174491 | 102.58 | 4.00 | UN | 51.29 | 12483243 | 4 | 168717 | 0365T | 2017-10-17 16:49:47 | 370211 | | 622843915 | 2017-10-06 18:58:57 24015783 | 256.45 | 2132206 | 174491 | 25.65 | 1.00 | UN | 51.29 | 12483244 | 1 | 168717 | 0364T | 2017-10-17 16:49:47 | 370211 | | 622843915 | 2017-10-06 18:58:57

Upvotes: 0

Views: 66

Answers (2)

Aditi Singh
Aditi Singh

Reputation: 117

Using your query and I myself created the query 

Select  
  ci.claimamount,
  sum(be.paidamount)
from 
   customers.billing_entries be 
   inner join (select distinct(claimid), claimamount from customers.claim_items) as ci 
      on be.lastclaimid = ci.claimid
      and be.lastclaimid = 2132206 
group by ci.claimamount;

I am getting the correct data as ouptut i.e claimamount 256.45 and total paidamount as 244.44. Edit -- Updated Answer according to the screenshots of table provided.

Upvotes: 0

Beth
Beth

Reputation: 9617

Not sure if this will help, but try grouping the amounts paid in a subquery:

select
    claimid, claimamount, sum_paid 
from
    customers.claim_items c inner join
    (select
        lastclaimid,
        sum(paidamount) as sum_paid
    from
        customers.billing_entries
    group by
        lastclaimid) p on
    c.claimid = p.lastclaimid

Upvotes: 1

Related Questions