Pulling Data from Multiple Aggregate Levels

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

SQL Server Development (2000)

Pulling Data from Multiple Aggregate Levels

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-15 : 10:29:34

Michael writes "Assume:
Account level (base) table
Household level (aggregate) table
Branch level (further summarized aggregate) table

The question is:
Show me branches with the corresponding households and each account they own only when -
Account Income < 50 AND Household Income > 100

To get this answer, I need to summarize from the account level (all accounts with an Income < 50) and summarize them into households so that I can get a new Household Income value.
I cannot just pull the Household Income from the Household aggregate table because that number could include accounts with Income > 50. Once I have the new Household Income values, I need to filter them for only those records that have a value > 100.

The trick, though, is that I need to keep the account level detail as well, because I'm trying to produce a report that
looks like this:

Branch #101 Income $100

HH #203 Income $ 40
Acct #424 Income $ 20
Acct #387 Income $ 20

HH #498 Income $ 60
Acct #539 Income $ 25
Acct #499 Income $ 20
Acct #638 Income $ 15

I am trying to do this through joins as opposed to using temporary tables to store intermediate results. Does anyone
know how to approach this?

Thanks for any insight,

Michael"

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-05-15 : 17:48:41

How about something like this:

CREATE TABLE #Branch(BranchNum int)
CREATE TABLE #HH(HHNum int, BranchNum int)
CREATE TABLE #Account(AcctNum int, HHNum int, income money)

SET NOCOUNT ON
INSERT INTO #Branch(BranchNum) VALUES(1)
INSERT INTO #Branch(BranchNum) VALUES(2)
INSERT INTO #Branch(BranchNum) VALUES(3)

INSERT INTO #HH(HHNum, BranchNum) VALUES(11,1)
INSERT INTO #HH(HHNum, BranchNum) VALUES(22,2)
INSERT INTO #HH(HHNum, BranchNum) VALUES(33,3)
INSERT INTO #HH(HHNum, BranchNum) VALUES(12,1)
INSERT INTO #HH(HHNum, BranchNum) VALUES(23,2)
INSERT INTO #HH(HHNum, BranchNum) VALUES(34,3)

INSERT INTO #Account(AcctNum, HHNum, income) VALUES(111, 11, $10)
INSERT INTO #Account(AcctNum, HHNum, income) VALUES(112, 11, $12)
INSERT INTO #Account(AcctNum, HHNum, income) VALUES(113, 12, $14)
INSERT INTO #Account(AcctNum, HHNum, income) VALUES(114, 12, $16)

INSERT INTO #Account(AcctNum, HHNum, income) VALUES(221, 22, $20)
INSERT INTO #Account(AcctNum, HHNum, income) VALUES(222, 22, $22)
INSERT INTO #Account(AcctNum, HHNum, income) VALUES(223, 23, $24)
INSERT INTO #Account(AcctNum, HHNum, income) VALUES(224, 23, $26)

INSERT INTO #Account(AcctNum, HHNum, income) VALUES(331, 33, $30)
INSERT INTO #Account(AcctNum, HHNum, income) VALUES(332, 33, $32)
INSERT INTO #Account(AcctNum, HHNum, income) VALUES(333, 34, $34)
INSERT INTO #Account(AcctNum, HHNum, income) VALUES(334, 34, $36)

Select b.BranchNum, hh.HHNum, a.AcctNum, a.income
FROM #Account a
INNER JOIN #HH hh on hh.HHNum = a.HHNum
INNER JOIN #Branch b ON b.BranchNum = hh.BranchNum

Select b.BranchNum, SUM(a.income) as BranchTotals from #Account a
INNER JOIN #HH hh on hh.HHNum = a.HHNum
INNER JOIN #Branch b ON b.BranchNum = hh.BranchNum
GROUP BY b.BranchNum

Select hh.HHNum, SUM(a.income) as HHTotals from #Account a
INNER JOIN #HH hh on hh.HHNum = a.HHNum
INNER JOIN #Branch b ON b.BranchNum = hh.BranchNum
GROUP BY hh.HHNum

DROP TABLE #Branch
DROP TABLE #account
DROP TABLE #HH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources