CODEWITHSUNDEEP
sql-server-2008
Hamonbatra
Hamonbatra

Reputation: 178

query sum value from two tables

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

Upvotes: 0

Views: 61

Answers (2)

Ron Smith
Ron Smith

Reputation: 3266

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

Upvotes: 1

cyan
cyan

Reputation: 747

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

Upvotes: 0

Related Questions