differnce between 2 aggregate columns

Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
Please post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegr oups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegr oups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||If I cut and paste those statements, they don't work.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegr oups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:

>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP
|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?