Join with groupby - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Join with groupby

Author

Topic

nkpriya
Starting Member

34 Posts

Posted - 2009-12-17 : 12:48:20

HI, I am in the process of learning SQL and now I am stuck with one of the queries:
I have two tables: one table has Account#,Month, CostCenter,DivisionName,ExpenseActual
and the second table has Account#,Month,CostCenter,DivisionName,Budget

I need to join these two tables with result - Account#,Month,CostCenter,DivisionName,ExpenseActual,Budget.

As I have several rows for Actual and Budget for each cost center, I guess I need to use sum function, but I am not sure what I need to use in Group by clause as I have similar columns in both tables.
Please help!
Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-17 : 13:04:58

select a.account#,a.month,a.costcenter,a.divisionname,sum(a.expenseactual)expenseactual,
b.budget as budget
from onetable a
inner join twotable b on b.account# = a.account#
group by a.account#,a.month,a.costcenter,a.divisionname

nkpriya
Starting Member

34 Posts

Posted - 2009-12-17 : 13:17:14

Thank you, but I need to use sum function on Budget too as I have more than one row for Budget for each cost center.
Please let me know.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-17 : 13:19:18

quote:
Originally posted by nkpriya

HI, I am in the process of learning SQL and now I am stuck with one of the queries:
I have two tables: one table has Account#,Month, CostCenter,DivisionName,ExpenseActual
and the second table has Account#,Month,CostCenter,DivisionName,Budget

I need to join these two tables with result - Account#,Month,CostCenter,DivisionName,ExpenseActual,Budget.

As I have several rows for Actual and Budget for each cost center, I guess I need to use sum function, but I am not sure what I need to use in Group by clause as I have similar columns in both tables.
Please help!
Thanks

hard to say without sample data...but since you have mentioned.."As I have several rows for Actual and Budget for each cost center"...can u try this...

select a.account#,a.month,a.costcenter,a.divisionname,a.expenseactual,
b.budget
from (select Account#,Month, CostCenter,DivisionName,sum(ExpenseActual) as ExpenseActual from table1 group by Account#,Month, CostCenter,DivisionName)  a
inner join (select Account#,Month, CostCenter,DivisionName,sum(Budget) as Budget from table2 group by Account#,Month, CostCenter,DivisionName) b 
on b.Account# = a.Account# and a.Month = b.Month and  a.CostCenter = b.CostCenter and a.DivisionName = b.DivisionName

Also why do you have two tables in the first place...Cant you just have budget as another column in table 1

nkpriya
Starting Member

34 Posts

Posted - 2009-12-17 : 13:57:02

I have two tables, I am pulling data from different database..Actuals table has many columns other than what I mentioned.

here is the sample data:
Actuals:
Month Account Actual CostCenter DiV
2009-11 Benefits 9,704.76 12604 Cash Mgmt
2009-11 Exempt 749.49 12606 Wealth Mgmt
2009-11 Benefits 813.70 12606 Wealth Mgmt
2009-11 Benefits 4,461.75 12607 Portfolio
2009-11 Communication 52,159.94 12607 Portfolio
2009-11 Benefits 6134.44 12609 Admin
2009-11 Benefits 7,611.72 12609 Admin
2009-11 Software 6,002.70 12613 Commercial Card Mgmt

Budget:
Month Account Budget CostCenter DiV
2009-11 Benefits 100.00 12604 Cash Mgmt
2009-11 Exempt 100.00 12606 Wealth Mgmt
2009-11 Exempt 100.00 12606 Wealth Mgmt
2009-11 Benefits 100.00 12607 Portfolio
2009-11 Communication 100.00 12607 Portfolio
2009-11 Benefits 100.00 12609 Admin
2009-11 Exempt 100.00 12609 Admin
2009-11 Software 100.00 12613 Commercial Card Mgmt

I tried using your query, but I am getting error message as incorrect syntax near '='

Please help!
Thanks

nkpriya
Starting Member

34 Posts

Posted - 2009-12-17 : 14:22:35

Now, it is working fine.

I am using left outer join in place of inner join and total of actuals is correct. However, I am not getting some rows from Budget table. Please let me know your thoughts.

Thanks

nkpriya
Starting Member

34 Posts

Posted - 2009-12-17 : 14:29:27

Figured it out. I am using Full Outer JOin and it is working fine. Thank you for all your help!

nkpriya
Starting Member

34 Posts

Posted - 2010-02-01 : 12:49:28

Hi, How can I use join in select stataments if I need to join 3 tables -

Below is the one I am using it to join 2 tables. Please let me know how can I join 3rd table:

3rd table has the same columns as the first two, the only difference is I have forecasted numbers in the 3rd table and first two tables has Budget and Actuals. Please let me know.

select a.account#,a.month,a.costcenter,a.divisionname,a.expenseactual,
b.budget
from (select Account#,Month, CostCenter,DivisionName,sum(ExpenseActual) as ExpenseActual from table1 group by Account#,Month, CostCenter,DivisionName) a
inner join (select Account#,Month, CostCenter,DivisionName,sum(Budget) as Budget from table2 group by Account#,Month, CostCenter,DivisionName) b
on b.Account# = a.Account# and a.Month = b.Month and a.CostCenter = b.CostCenter and a.DivisionName = b.DivisionName

Thanks
Krishna

MrQuizzles
Starting Member

20 Posts

Posted - 2010-02-01 : 13:08:40

Joining a 3rd table is done simply by tacking on another join statement.

Short example:


SELECT * FROM table1
JOIN table2 ON table1.id = table2.relativeID
JOIN table3 ON Table1.id = table3.relativeID

To join a 4th, just add yet another JOIN statement, and so on as long as you can join the table to the resultant table created by the previous joins.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources