query sum value from two tables

Question

i am facing problem with sum payment_value : enter image description here

suppose i inserted to payment_value in families payments 200$ to father and mother and 300$ in childrenpayments for each child 100$ .. now i want to query this to sum payment_value for this family .. i want it like this table result

i want sql query like this :

SELECT 
    family_id,
    father_name,
    mother_name,
    last_name,
    'children count' AS [here count children for each family],
    'sumpayment' AS [here i want sum payment for father and mother and children]
FROM dbo.Families

Ron Smith · Accepted Answer · 2014-02-23 01:52:41Z

select
    f.family_id,
    f.father_name,
    f.mother_name,
    f.last_name,
    cp.children_count,
    isnull(cp.children_payment,0) + isnull(fp.families_payment,0) sumpayment
FROM dbo.Families f
    inner join  (
                select
                    c.family_id,
                    count(distinct c.child_id) children_count,
                    sum(p.payment_value) children_payment
                from dbo.Children c
                    inner join ChildrenPayments p
                        on p.child_id = c.child_id
                group by
                    c.family_id
                ) cp
        on cp.family_id = f.family_id
    inner join  (
                select
                    p.family_id,
                    sum(p.payment_value) families_payment
                from familiesPayments p
                group by
                    p.family_id
                ) fp
        on fp.family_id = f.family_id

SLQ Fiddle Link: http://www.sqlfiddle.com/#!2/d7e43/13

cyan · Accepted Answer · 2014-02-23 01:42:26Z

0

Try this

SELECT 
f.family_id, f.father_name, f.mother_name, f.last_name, count(c.childId) AS TotalChildren
, sum(fp.paymentValue + cp.paymentValue)
FROM dbo.Families f inner join familiesPayment fp on f.family_id = fp.family_id inner join children c on f.family_id = c.family_id inner join childrenPayment cp on c.child_id = cp.child_id
group by  f.family_id, f.father_name, f.mother_name, f.last_name

edited Feb 23, 2014 at 1:42

answered Feb 23, 2014 at 1:31

cyan

7475 silver badges8 bronze badges

3 Comments

Ron Smith Over a year ago

The problem here is that for every family payment, it will include all the child records. This will exaggerate the counts and sums. I would count/sum each entity grouped by family_id and join those to the family table. See below.

cyan Over a year ago

the group by is out side the code, please include it

Ron Smith Over a year ago

Yes, it is still going to over sum the payments. Here is a SQLFiddle Link. The first query is mine, the second is yours. With 3 children each getting 100, and the family getting 200, it should come to 500. Your query comes to 900. sqlfiddle.com/#!2/d7e43/12

Collectives™ on Stack Overflow

query sum value from two tables

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related