Sum Distinct In Joint? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sum Distinct In Joint?

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-08-14 : 14:05:16

Hi,

I have in my database a family table:

CREATE TABLE tempFamily (familyID smallint PRIMARY KEY IDENTITY, familyName nvarchar(10));
INSERT INTO tempFamily VALUES ('FamilyA');
INSERT INTO tempFamily VALUES ('FamilyB');
INSERT INTO tempFamily VALUES ('FamilyC');

And each family has members and their annual salary:

CREATE TABLE tempFamilyMember (memberID smallint PRIMARY KEY IDENTITY, familyID smallint, memberDesc nvarchar(50), annualSalary money);
INSERT INTO tempFamilyMember VALUES (1, 'Dad_A', 100);
INSERT INTO tempFamilyMember VALUES (1, 'Mom_A', 100);
INSERT INTO tempFamilyMember VALUES (1, 'Son_A', 80);
INSERT INTO tempFamilyMember VALUES (2, 'Mom_B', 200);
INSERT INTO tempFamilyMember VALUES (2, 'Daughter_B', 160);
INSERT INTO tempFamilyMember VALUES (3, 'Son_C', 120);

Every year some of these families receive subsidies:

CREATE TABLE tempSubsidy (subsidyID smallint PRIMARY KEY IDENTITY, familyID smallint, subsidyYear smallint, subsidyAmount money);
INSERT INTO tempSubsidy VALUES (1, 2005, 1000);
INSERT INTO tempSubsidy VALUES (1, 2006, 1200);
INSERT INTO tempSubsidy VALUES (1, 2007, 1000);
INSERT INTO tempSubsidy VALUES (2, 2006, 1200);
INSERT INTO tempSubsidy VALUES (2, 2007, 1000);
INSERT INTO tempSubsidy VALUES (3, 2007, 600);

Now how do I write a single query that would return an annual report of how much subsidy was given each year and the total salary of the benefit family like so?

Year Total Subsidy Total Salary
2005 $1000 $280
2006 $2400 $640
2007 $2600 $760

Thanks,
ywb

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 14:17:14

select s.subsidyyear as Year, sum(s.subsidyAmount) as [Total Subsidy], sum(FM.annualSalary)
from tempSubsidy S, tempFamilyMember FM
where s.familyid = FM.familyid
group by subsidyyear

Ashley Rhodes

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-14 : 14:22:48

SELECT ts.subsidyYear
,'TotalSubsidy' = SUM(ts.subsidyAmount)
,'TotalSalary' = SUM(tfm.annualSalary )
FROM tempSubsidy ts
INNER JOIN
tempFamilyMember tfm
ON
ts.familyid = tfm.familyid
GROUP BY ts.subsidyYear
ORDER BY subsidyYear

Jim

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-08-14 : 14:33:18

Hi Ashley & Jim,

The queries you gave would mistakenly multiply the the subsidy amount. For instance, the subsidy in 2005 is $1000 but both your queries give $3000.

I can get what I want using a temp table like this:

CREATE TABLE tempTotalSubsidy (subsidyYear smallint, totalSubsidy smallint);
INSERT INTO tempTotalSubsidy
SELECT subsidyYear, SUM(subsidyAmount)
FROM dbo.tempSubsidy
GROUP BY subsidyYear;

SELECT sub.subsidyYear, SUM(mem.annualSalary) AS sumSalary, tempTotalSubsidy.totalSubsidy AS sumSubsidy
FROM dbo.tempFamily fam
INNER JOIN dbo.tempSubsidy sub ON fam.familyID = sub.familyID
INNER JOIN dbo.tempFamilyMember mem ON fam.familyID = mem.familyID
INNER JOIN tempTotalSubsidy ON sub.subsidyYear = tempTotalSubsidy.subsidyYear
GROUP BY sub.subsidyYear, tempTotalSubsidy.totalSubsidy
ORDER BY sub.subsidyYear;

But I find that quite cumbersome and wondering if I can do with just a single query.

Thanks,
ywb

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-14 : 14:36:57

What are the joins between the tables (i.e, the linking fields)
This doesn't quite give it either

SELECT ts.subsidyYear
,'TotalSubsidy' = SUM(ts.subsidyAmount)
,'TotalSalary' = SUM(tfm.annualSalary )
FROM #tempSubsidy ts
INNER JOIN
#tempFamilyMember tfm
ON
ts.subsidyid = tfm.memberid
GROUP BY ts.subsidyYear
ORDER BY 1

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-14 : 14:42:47

SELECT ts.subsidyYear
,'TotalSubsidy' = SUM(ts.subsidyAmount)
,'TotalSalary' = SUM(tf.annualSalary )
FROM #tempSubsidy ts
INNER JOIN
#tempFamilyMember tfm
ON
ts.subsidyid = tfm.memberid
INNER JOIN
(select familyID,'AnnualSalary' = sum(annualSalary)
from #tempFamilyMember
group by familyID) tf
ON
ts.familyId = tf.familyID

GROUP BY ts.subsidyYear
ORDER BY 1

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 14:44:32

The above query is wrong.
Is this not what you are looking for:

select s.familyid, s.Year, sum(s.[Total Subsidy])as [Total Subsidy], FM.salary
FROM
(select familyid, subsidyyear as Year, sum(subsidyAmount) as [Total Subsidy]
from tempSubsidy
group by subsidyyear, familyid) S

left join

(select familyid, sum(annualSalary)as Salary
from tempFamilyMember
group by familyID) FM

on s.familyid = FM.familyid

group by s.Year, FM.SALARY, s.familyid
order by s.familyid

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 14:46:49

The requirement you have stated does not lists any FamilyID in it. So even if you have salary in it
how do you know who that salary belongs to. Or that does not matter.

Ashley Rhodes

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-08-14 : 14:49:29

Hi Jim, the 3 tables are joined by the familyID field.

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-08-14 : 14:52:45

Hi Ashley, it does not matter; I just need a summary of each year.

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-08-14 : 14:54:34

Hi Jim, thanks! This one works.

quote:
Originally posted by jimf

SELECT ts.subsidyYear
,'TotalSubsidy' = SUM(ts.subsidyAmount)
,'TotalSalary' = SUM(tf.annualSalary )
FROM #tempSubsidy ts
INNER JOIN
#tempFamilyMember tfm
ON
ts.subsidyid = tfm.memberid
INNER JOIN
(select familyID,'AnnualSalary' = sum(annualSalary)
from #tempFamilyMember
group by familyID) tf
ON
ts.familyId = tf.familyID

GROUP BY ts.subsidyYear
ORDER BY 1

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-14 : 14:58:13

SELECT ts.subsidyYear
,'TotalSubsidy' = SUM(ts.subsidyAmount)
,'TotalSalary' = SUM(tfm.annualSalary )
FROM #tempSubsidy ts
INNER JOIN
(select familyID,'AnnualSalary' = sum(annualSalary)
from #tempFamilyMember
group by familyID)tfm
ON
ts.familyID = tfm.familyID

GROUP BY ts.subsidyYear

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources