December 21, 2005 at 2:45 pm
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
AccountNumName
--------------------
100Fuel
200Tires
Table: SubAccount
AccountNumSubAccountNumName
--------------------------------------------
100101Diesel
100102Gasoline
200200Winter Tire
200201All Season Tire
Table: Trans (transactions)
SubAccountNumAmountType (A-Actual, B-Budget)
-------------------------------------------------
10110A
10220A
20030A
20140A
10150B
10260B
20070B
20180B
From the data above, I need to end up with output from my Select as follows:
NameActualBudget
----------------------
Fuel30110
Tires70150
December 21, 2005 at 2:55 pm
You only need to join to Trans once, and do conditional summing based on the type:
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
December 21, 2005 at 3:32 pm
Thank you, that worked perfectly! Clearly I need to dive into SQL joins.
December 21, 2005 at 3:43 pm
you don't have to ....choose one..
select a.name
, actual = sum(ta.amount)
, budget = sum(tb.amount)
from Account a
, SubAccount sa
, Trans ta, Trans tb
where a.AccountNum = sa.AccountNum
and sa.SubAccountNum = ta.SubAccountNum
and sa.SubAccountNum = tb.SubAccountNum
and ta.Type = 'A' and tb.Type ='B'
group by a.name
--==========================================
select a.name
, actual = sum(CASE t.Type WHEN 'A' THEN t.amount END)
, budget = sum(CASE t.Type WHEN 'B' THEN t.amount END)
from Account a
, SubAccount sa
, Trans t
where a.AccountNum = sa.AccountNum
and sa.SubAccountNum = t.SubAccountNum
group by a.name
December 21, 2005 at 4:29 pm
Okay, now I have a solution using a join and 2 solutions without the use of a join. What should I be considering in selecting one of these solutions? Can I use a tool (such as SQL Analyzer) to determine the most optimized code (I only have a sampling of the data though)? In general, are joins a better approach, or does it depend on the makeup of the data?
December 21, 2005 at 4:42 pm
All of the code above is "joining" the data, the difference is just in the syntax.
Joining using the WHERE part is old-style SQL, using the INNER JOIN syntax in the FROM is ANSI SQL-92 standard and portable to other SQL-92 compliant platforms.
Comes down to personal preference. Mostly.
However, consider the case of when OUTER joins are required. The old-style Sql Server syntax for this is "*=" or "=*" in the WHERE, but Microsoft may not support this in the future. However, since LEFT OUTER JOIN and RIGHT OUTER JOIN etc are part of the ANSI SQL-92 standard, they will be supported in the future and are portable. Your call which method to adopt.
[Edit] In Query Analyser, hit CTRL-L to get an execution plan of the queries to see how Sql Server is optimizing them.
December 22, 2005 at 8:40 am
Also, the fewer joins you use the better. I liked the solution that used the CASE statement. I recommend that because it seems more logical and minimizes the JOINs.
No one should continue using the old style join syntax with table names separated by columns unless you are maintaining SQL on a legacy platform. There are subtle differences in each vendor's version but they all support JOIN and there are few if any differences. I wouldn't say this is personal preference any more.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply