Problem Using Alias

I need a select that sums the same column, but with different criteria.
I also need to "roll up" the sums into a "higher level" sum based on
some other tables. This is an accounting database, where subaccounts
roll up into a single account (i.e. subaccounts 101 and 102 roll up
into account 100).
I'm using an alias, but not getting correct results. I believe I'm
ending up with a cartesian product, but not sure of the "fix". Note: I
cannot change the table design, this is an existing accounting database
created by a different application.
Here's a simplified version of the query with tables following that:
SELECT Account.Name, Sum(A.Amount) as Actual, Sum(B.Amount) as Budget
FROM Account, SubAccount, Trans A, Trans B
WHERE
Account.AccountNum = SubAccount.AccountNum and
Trans.SubAccountNum = SubAccount.SubAccountNum and
A.Trans.Type = 'A' and B.Trans.Type = 'B'
GROUP BY Account.Name
What's wrong with my SQL above?
Here are my tables with sample data:
Table: Account
AccountNum Name
--
100 Fuel
200 Tires
Table: SubAccount
AccountNum SubAccountNum Name
---
100 101 Diesel
100 102 Gasoline
200 200 Winter Tire
200 201 All Season Tire
Table: Trans (transactions)
SubAccountNum Amount Type (A-Actual, B-Budget)
---
101 10 A
102 20 A
200 30 A
201 40 A
101 50 B
102 60 B
200 70 B
201 80 B

>From the data above, I need to end up with output from my Select as
follows:
Name Actual Budget
--
Fuel 30 110
Tires 70 150> FROM Account, SubAccount, Trans A, Trans B
Please use ANSI inner joins. It will make your query easier to read and
will allow you to

> Trans.SubAccountNum = SubAccount.SubAccountNum and
> A.Trans.Type = 'A' and B.Trans.Type = 'B'
You're saying, instead of calling Trans "Trans", let's call it "A". What do
you expect A.Trans to mean? Didn't you mean
A.SubAccountNum = SubAccount.SubAccountNum
AND
A.Type = 'A'
AND B.Type = 'B'
I have a better way to formulate the query however, because of my first
point, I have no idea whether two copies of the Trans table are required,
how they each relate to Account and SubAccount, and how the sums should be
generated. If you provide more details I'd be more than happy to write you
a more readable query. Please see: http://www.aspfaq.com/5006|||As you said, I should be using joins. The code below did the trick.
Thanks for your input.
SELECT Account.Name,
Sum(Case Trans.Type When 'A' Then Trans.Amount Else 0 End) as Actual,
Sum(Case Trans.Type When 'B' Then Trans.Amount Else 0 End) as Budget
FROM Account
INNER JOIN SubAccount
ON Account.AccountNum = SubAccount.AccountNum
INNER JOIN Trans
ON Trans.SubAccountNum = SubAccount.SubAccountNum|||> Please use ANSI inner joins. It will make your query easier to read and
> will allow you to
finish sentences! What I was going to elaborate on about is that you can
separate join and filter criteria. Looks like you've already got a handle
on it in this case.|||CREATE TABLE Accounts (AccountNum int NOT NULL, [name] varchar(25), PRIMARY
KEY (AccountNum))
GO
CREATE TABLE SubAccounts (AccountNum int REFERENCES Accounts(AccountNum),
SubAccountNum int, [name] varchar(25))
GO
INSERT INTO Accounts VALUES (100, 'Fuel')
INSERT INTO Accounts VALUES (200, 'Tires')
INSERT INTO SubAccounts VALUES (100,101,'Diesel')
INSERT INTO SubAccounts VALUES (100,102,'Gasoline')
INSERT INTO SubAccounts VALUES (200,200,'Winter Tire')
INSERT INTO SubAccounts VALUES (200,201,'All Season Tire')
CREATE TABLE Trans
(SubAccountNum int, Amount int, Type char(1))
GO
INSERT INTO test4 VALUES (101,10,'A')
INSERT INTO test4 VALUES (102,20,'A')
INSERT INTO test4 VALUES (200,30,'A')
INSERT INTO test4 VALUES (201,40,'A')
INSERT INTO test4 VALUES (101,50,'B')
INSERT INTO test4 VALUES (102,60,'B')
INSERT INTO test4 VALUES (200,70,'B')
INSERT INTO test4 VALUES (201,80,'B')
SELECT a.[name], SUM(t1.TotalActual) AS "Total Actual",
SUM(t2.TotalBudgeted) AS "Total Budgeted"
FROM Accounts a INNER JOIN SubAccounts sa ON a.AccountNum=sa.AccountNum
INNER JOIN (SELECT SubAccountNum, SUM(Amount) AS "TotalActual" FROM Trans
WHERE Type='A'
GROUP BY SubAccountNum) t1 ON t1.SubAccountNum=sa.SubAccountNum
INNER JOIN (SELECT SubAccountNum, SUM(Amount) AS "TotalBudgeted" FROM Trans
WHERE Type='B'
GROUP BY SubAccountNum) t2 ON t2.SubAccountNum=sa.SubAccountNum
GROUP BY a.[name]
"joeacunzo@.yahoo.com" wrote:

> I need a select that sums the same column, but with different criteria.
> I also need to "roll up" the sums into a "higher level" sum based on
> some other tables. This is an accounting database, where subaccounts
> roll up into a single account (i.e. subaccounts 101 and 102 roll up
> into account 100).
> I'm using an alias, but not getting correct results. I believe I'm
> ending up with a cartesian product, but not sure of the "fix". Note: I
> cannot change the table design, this is an existing accounting database
> created by a different application.
> Here's a simplified version of the query with tables following that:
> SELECT Account.Name, Sum(A.Amount) as Actual, Sum(B.Amount) as Budget
> FROM Account, SubAccount, Trans A, Trans B
> WHERE
> Account.AccountNum = SubAccount.AccountNum and
> Trans.SubAccountNum = SubAccount.SubAccountNum and
> A.Trans.Type = 'A' and B.Trans.Type = 'B'
> GROUP BY Account.Name
> What's wrong with my SQL above?
> Here are my tables with sample data:
> Table: Account
> AccountNum Name
> --
> 100 Fuel
> 200 Tires
> Table: SubAccount
> AccountNum SubAccountNum Name
> ---
> 100 101 Diesel
> 100 102 Gasoline
> 200 200 Winter Tire
> 200 201 All Season Tire
> Table: Trans (transactions)
> SubAccountNum Amount Type (A-Actual, B-Budget)
> ---
> 101 10 A
> 102 20 A
> 200 30 A
> 201 40 A
> 101 50 B
> 102 60 B
> 200 70 B
> 201 80 B
>
> follows:
> Name Actual Budget
> --
> Fuel 30 110
> Tires 70 150
>