Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  Join 2 tables and calculate SUM Reply to Topic  Printer Friendly
Author  Topic
Page: of 2

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/22/2013 :  10:52:12 I have 2 table (Table1 and Table2)Table1 look like thisID, Company(1,'Company1')(1,'Company1')Table2 look like thisCompany, Date, Bill('Company1', 2013-01-10, 1000)('Company1', 2013-03-12, 1200)('Company2', 2013-01-11, 1300)('Company1', 2013-01-12, 1800)('Company2', 2013-02-10, 2000)('Company2', 2013-01-15, 2300)('Company1', 2013-01-22, 1100)('Company2', 2013-03-29, 3300)('Company1', 2013-02-07, 3100)and when I join that tables it's look like this('Company1', 2013-01-10, 1000)('Company1', 2013-01-12, 1800)('Company1', 2013-01-22, 1100)('Company2', 2013-01-11, 1300)('Company2', 2013-01-15, 2300)I selected only january AND I need to calculate SUM for Company's like this('Company1',3900)('Company2',3600)

James K
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 04/22/2013 :  11:09:51 ```select t1.Company, SUM(t2.Bill) as Total from Table1 t1 left join Table2 t2 on t1.Company = t2.Company group by t1.Company order by t1.Company```You can use an INNER JOIN instead of LEFT JOIN if you don't want to include companies that are in Table1, but don't have any row in Table2.

djj55
Constraint Violating Yak Guru

USA
352 Posts

 Posted - 04/22/2013 :  11:10:14 First query`SELECT tb2.* FROM tb2 INNER JOIN tb1 ON tb2.company = tb1.company order by 1,2`second query```SELECT tb2.company, sum(bill) as tbill FROM tb2 INNER JOIN tb1 ON tb2.company = tb1.company where month(tb2.[date]) = 1 group by tb2.company order by 1```Please look up what was done here.djj

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/22/2013 :  12:26:49 i got errorcolumn '' is invalid in the select list because is it not contained in either an aggregate function or the GROUP BY clause

djj55
Constraint Violating Yak Guru

USA
352 Posts

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/22/2013 :  15:30:08 select s.NAZIV as Firma, p.DATUM, p.ZAPLACILO as Zbir from SUBJEKT s left join PROMET p on s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000' and (month(DATUM) = 1 and year(DATUM) = 2013) group by s.NAZIV, p.DATUM, p.ZAPLACILO order by s.NAZIV

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/22/2013 :  15:31:56 this real table have a lot columns but me only need some.Also column s.NAZIV and p.PREJEMNIK are same. It is a names of companys

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

 Posted - 04/23/2013 :  01:12:55 Above query doesn't have any issues.. show us the complete code.. Rule: Any non-aggregate columns in the SELECT must be exist on GROUP BY clause--Chandu

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/23/2013 :  12:37:42 That is complete code...this is complete error...Column 'PROMET.ZAPLACILO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

 Posted - 04/23/2013 :  13:03:30 Strange if that is a complete code and coming up with this error. Anyhow, why are you grouping when there is no aggregates in the Select statement??? just to remove duplicates? If, so why don't you simply use Distinct??CheersMIK

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/23/2013 :  13:09:37 I try but somethin doesnt work..

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

 Posted - 04/23/2013 :  13:18:05 Execute the query without Group By clause... and let us know if it runs successfully? SELECT s.NAZIV as Firma,p.DATUM,p.ZAPLACILO as ZbirFROM SUBJEKT s left join PROMET p on s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000' and (month(DATUM) = 1 and year(DATUM) = 2013)If it does, then run it with Distinct, as followingSELECT Distincts.NAZIV as Firma,p.DATUM,p.ZAPLACILO as ZbirFROM SUBJEKT s left join PROMET p on s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000' and (month(DATUM) = 1 and year(DATUM) = 2013)If number of records for both queries are same then you can use the first query, else grouping in your query is actually meant for restricting the duplication - which can be accomplished by using Distinct In case of any error, please provide the full error message along with the query which you've executed.CheersMIK Edited by - MIK_2008 on 04/23/2013 13:18:59

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/23/2013 :  13:35:35 No errors but that is not what I looking for... You can see in my 1 post what i try to cacluatae. Sum of some rows by date...

djj55
Constraint Violating Yak Guru

USA
352 Posts

 Posted - 04/23/2013 :  13:50:55 Is something like this what you are looking for?```select s.NAZIV as Firma, SUM(p.ZAPLACILO) as Zbir from SUBJEKT s left join PROMET p on s.NAZIV=p.PREJEMNIK where p.POSLDOG='3000' and (month(DATUM) = 1 and year(DATUM) = 2013) group by s.NAZIV, order by s.NAZIV```djj

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

 Posted - 04/23/2013 :  13:53:05 DATUM seems to store date informationNAZIV and ZAPLACILO stores company name. then which table's field is storing the bill information? and that field is in SUBJEKT or PROMET??The resulting query would somehow be as followingSELECT NAZIV,ZAPLACILO, Sum(ISNULL(,0)) SumTotalFROM GROUP BY NAZIV,ZAPLACILO -- these are the two fields (non-aggregate columns) that should only be in the group by clause, sum(bill) is acually an aggregate columnCheersMIK Edited by - MIK_2008 on 04/23/2013 13:53:39

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/23/2013 :  14:10:38 select p.PREJEMNIK, sum (p.ZAPLACILO) as SumaJanuar from PROMET p left join SUBJEKT son s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000'and (month(DATUM) = 1 and year(DATUM) = 2013)group by p.PREJEMNIKorder by p.PREJEMNIKThis is OK. I calculate for January. And how to calculate in same query for February, March and sum(janury + february + march )

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/23/2013 :  14:16:01 ZAPLACILO is billNAZIV is Company from table SUBJEKTPREJEMNIK is Company from table PROMET

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

 Posted - 04/23/2013 :  14:16:08 and (month(DATUM) = 2 and year(DATUM) = 2013) -- Feb 2013and (month(DATUM) = 3 and year(DATUM) = 2013) -- Mar 2013and (month(DATUM) = 4 and year(DATUM) = 2013) -- Apr 2013....and so onCheersMIK

nikoz
Yak Posting Veteran

63 Posts

 Posted - 04/23/2013 :  14:20:19 Yes like this... and (month(DATUM) = 1 and year(DATUM) = 2013) -- Jan 2013and (month(DATUM) = 2 and year(DATUM) = 2013) -- Feb 2013and (month(DATUM) = 3 and year(DATUM) = 2013) -- Mar 2013..

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

 Posted - 04/23/2013 :  14:20:21 quote:Originally posted by nikozThis is OK. I calculate for January. And how to calculate in same query for February, MarchInfact, for Feb replace and (month(DATUM) = 1 and year(DATUM) = 2013) condition with,and (month(DATUM) = 2 and year(DATUM) = 2013) -- Feb 2013and for Mar replace with and (month(DATUM) = 3 and year(DATUM) = 2013) -- Mar 2013quote:and sum(janury + february + march )i think this is what you want to use at the bottom of some report? if thats the case, do it on the application/report designer levelCheersMIK Edited by - MIK_2008 on 04/23/2013 14:21:54

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

 Posted - 04/23/2013 :  14:24:06 This is how you can have "company and month" wise sum for the Jan, Feb, and March. select p.PREJEMNIK, month(DATUM), sum (p.ZAPLACILO) as SumaJanuar from PROMET p left join SUBJEKT son s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000'and (month(DATUM) in(1,2,3) and year(DATUM) = 2013)group by p.PREJEMNIK, month(DATUM)order by p.PREJEMNIKCheersMIK
Page: of 2  Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC