Problem Using Alias

21 Dec 2005 9:43 PM

joeacunzo

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

21 Dec 2005 9:59 PM

Aaron Bertrand [SQL Server MVP]

> 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

21 Dec 2005 10:14 PM

joeacunzo

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

21 Dec 2005 10:18 PM

Aaron Bertrand [SQL Server MVP]

> 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.

21 Dec 2005 10:47 PM

Mark Williams

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]

Show quote

"joeacu***@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
>
> >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
>
>