SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join 2 tables and calculate SUM
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/22/2013 :  10:52:12  Show Profile  Reply with Quote
I have 2 table (Table1 and Table2)

Table1 look like this

ID, Company
(1,'Company1')
(1,'Company1')


Table2 look like this

Company, 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

3565 Posts

Posted - 04/22/2013 :  11:09:51  Show Profile  Reply with Quote
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.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
311 Posts

Posted - 04/22/2013 :  11:10:14  Show Profile  Reply with Quote
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
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/22/2013 :  12:26:49  Show Profile  Reply with Quote
i got error
column '' is invalid in the select list because is it not contained in either an aggregate function or the GROUP BY clause
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
311 Posts

Posted - 04/22/2013 :  12:28:47  Show Profile  Reply with Quote
Please show your code

djj
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/22/2013 :  15:30:08  Show Profile  Reply with Quote
select s.NAZIV as Firma,
p.DATUM,
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,
p.DATUM,
p.ZAPLACILO

order by s.NAZIV


Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/22/2013 :  15:31:56  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/23/2013 :  01:12:55  Show Profile  Reply with Quote
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
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  12:37:42  Show Profile  Reply with Quote
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.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/23/2013 :  13:03:30  Show Profile  Reply with Quote
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??

Cheers
MIK
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  13:09:37  Show Profile  Reply with Quote
I try but somethin doesnt work..
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/23/2013 :  13:18:05  Show Profile  Reply with Quote
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 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)

If it does, then run it with Distinct, as following

SELECT Distinct
s.NAZIV as Firma,
p.DATUM,
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)

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.

Cheers
MIK

Edited by - MIK_2008 on 04/23/2013 13:18:59
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  13:35:35  Show Profile  Reply with Quote
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...
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
311 Posts

Posted - 04/23/2013 :  13:50:55  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/23/2013 :  13:53:05  Show Profile  Reply with Quote
DATUM seems to store date information
NAZIV 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 following

SELECT NAZIV,ZAPLACILO, Sum(ISNULL(<TableAlias.FieldNameStoringBillInformation>,0)) SumTotal
FROM <your table joins logic should goes in here
and the where condition as well>
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 column

Cheers
MIK

Edited by - MIK_2008 on 04/23/2013 13:53:39
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  14:10:38  Show Profile  Reply with Quote
select p.PREJEMNIK, sum (p.ZAPLACILO) as SumaJanuar from
PROMET p left join SUBJEKT s
on s.NAZIV=p.PREJEMNIK
where p.POSLDOG='3000'
and (month(DATUM) = 1 and year(DATUM) = 2013)
group by p.PREJEMNIK
order by p.PREJEMNIK

This is OK. I calculate for January. And how to calculate in same query for February, March and sum(janury + february + march )
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  14:16:01  Show Profile  Reply with Quote
ZAPLACILO is bill
NAZIV is Company from table SUBJEKT
PREJEMNIK is Company from table PROMET
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/23/2013 :  14:16:08  Show Profile  Reply with Quote

and (month(DATUM) = 2 and year(DATUM) = 2013) -- Feb 2013
and (month(DATUM) = 3 and year(DATUM) = 2013) -- Mar 2013
and (month(DATUM) = 4 and year(DATUM) = 2013) -- Apr 2013
..
..
and so on

Cheers
MIK
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  14:20:19  Show Profile  Reply with Quote
Yes like this...

and (month(DATUM) = 1 and year(DATUM) = 2013) -- Jan 2013
and (month(DATUM) = 2 and year(DATUM) = 2013) -- Feb 2013
and (month(DATUM) = 3 and year(DATUM) = 2013) -- Mar 2013
..
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/23/2013 :  14:20:21  Show Profile  Reply with Quote
quote:
Originally posted by nikoz
This is OK. I calculate for January. And how to calculate in same query for February, March



Infact, for Feb replace
and (month(DATUM) = 1 and year(DATUM) = 2013)
condition with,
and (month(DATUM) = 2 and year(DATUM) = 2013) -- Feb 2013

and for Mar replace with
and (month(DATUM) = 3 and year(DATUM) = 2013) -- Mar 2013

quote:

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 level

Cheers
MIK

Edited by - MIK_2008 on 04/23/2013 14:21:54
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/23/2013 :  14:24:06  Show Profile  Reply with Quote
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 s
on s.NAZIV=p.PREJEMNIK
where p.POSLDOG='3000'
and (month(DATUM) in(1,2,3) and year(DATUM) = 2013)
group by p.PREJEMNIK, month(DATUM)
order by p.PREJEMNIK

Cheers
MIK
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000