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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join 2 tables and calculate SUM

Author  Topic 

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-22 : 10:52:12
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 11:09:51
[code]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[/code]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

352 Posts

Posted - 2013-04-22 : 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
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-22 : 12:26:49
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

352 Posts

Posted - 2013-04-22 : 12:28:47
Please show your code

djj
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-22 : 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.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

63 Posts

Posted - 2013-04-22 : 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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 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
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-23 : 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.
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-23 : 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??

Cheers
MIK
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-23 : 13:09:37
I try but somethin doesnt work..
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-23 : 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 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
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-23 : 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...
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-04-23 : 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
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-23 : 13:53:05
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
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-04-23 : 14:10:38
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

63 Posts

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-23 : 14:16:08

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

63 Posts

Posted - 2013-04-23 : 14:20:19
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-23 : 14:20:21
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
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-23 : 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 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
    Next Page

- Advertisement -