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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  14:29:39  Show Profile  Reply with Quote
Yes and that is TOTALSUM for all 3 mounth (Jan+feb+Mar).
I want im my resault COLUMNS like SumJun, SumFeb, SumMar, TOTALSUM.
I suppose need to join tables copule times to get that columns...
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  14:39:05  Show Profile  Reply with Quote
I want in same view that columns. Understad me what I try to say?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/23/2013 :  14:50:56  Show Profile  Reply with Quote
You may want to use case statement like this:


select p.PREJEMNIK, month(DATUM), 
	SUM (case when month(DATUM) = 1 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaJanuar 
	SUM (case when month(DATUM) = 2 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaFebruary
	SUM (case when month(DATUM) = 3 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaMarch
	SUM (case when month(DATUM) in (1, 2, 3) and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaTotal
	from 
PROMET p left join SUBJEKT s
on s.NAZIV=p.PREJEMNIK
where p.POSLDOG='3000'
group by p.PREJEMNIK, month(DATUM)
order by p.PREJEMNIK
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  14:58:57  Show Profile  Reply with Quote
I have 1 extra column and a lot NULLs and PREJEMNIK is reapeted...
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  15:02:07  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) in (1) and year(DATUM) = 2013)
group by p.PREJEMNIK
order by p.PREJEMNIK)
and
(select p.PREJEMNIK, sum (p.ZAPLACILO) as SumaFebruar from
PROMET p left join SUBJEKT s
on s.NAZIV=p.PREJEMNIK
where p.POSLDOG='3000'
and (month(DATUM) in (2) and year(DATUM) = 2013)
group by p.PREJEMNIK
order by p.PREJEMNIK)


I am trying somethink like this...

and error is

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'order'.

Edited by - nikoz on 04/23/2013 15:06:08
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/23/2013 :  15:09:00  Show Profile  Reply with Quote
quote:

You may want to use case statement like this:



select p.PREJEMNIK, month(DATUM),
SUM (case when month(DATUM) = 1 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaJanuar
SUM (case when month(DATUM) = 2 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaFebruary
SUM (case when month(DATUM) = 3 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaMarch
SUM (case when month(DATUM) in (1, 2, 3) and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaTotal
from
PROMET p left join SUBJEKT s
on s.NAZIV=p.PREJEMNIK
where p.POSLDOG='3000'
group by p.PREJEMNIK, month(DATUM)
order by p.PREJEMNIK





nikoz,
Did you try the query I posted earlier?
(Shown above)
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  15:11:41  Show Profile  Reply with Quote
Yes i tryed and I have one column extra and PREJEMNIK is not disticnt if you know what i mean...
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/23/2013 :  15:23:04  Show Profile  Reply with Quote
Can you show what you are getting and what you would like to get as output?
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  15:28:40  Show Profile  Reply with Quote
My mentor says that i need to join several times those tables to get results
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/24/2013 :  03:31:37  Show Profile  Reply with Quote
http://www.freeimagehosting.net/5mbuh
this is what i get
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 04/24/2013 :  05:07:24  Show Profile  Reply with Quote
--May be this?

select p.PREJEMNIK, 
	SUM (case when month(DATUM) = 1 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaJanuar 
	SUM (case when month(DATUM) = 2 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaFebruary
	SUM (case when month(DATUM) = 3 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaMarch
	SUM (case when month(DATUM) in (1, 2, 3) and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaTotal
	from 
PROMET p 
join SUBJEKT s
on s.NAZIV=p.PREJEMNIK
where p.POSLDOG='3000'
group by p.PREJEMNIK
order by p.PREJEMNIK


--
Chandu
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/24/2013 :  07:46:59  Show Profile  Reply with Quote
quote:
Originally posted by bandi

--May be this?

select p.PREJEMNIK, 
	SUM (case when month(DATUM) = 1 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaJanuar 
	SUM (case when month(DATUM) = 2 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaFebruary
	SUM (case when month(DATUM) = 3 and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaMarch
	SUM (case when month(DATUM) in (1, 2, 3) and year(DATUM) = 2013 THEN p.ZAPLACILO END) as SumaTotal
	from 
PROMET p 
join SUBJEKT s
on s.NAZIV=p.PREJEMNIK
where p.POSLDOG='3000'
group by p.PREJEMNIK
order by p.PREJEMNIK


--
Chandu



Yes
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
425 Posts

Posted - 04/24/2013 :  13:21:15  Show Profile  Reply with Quote
The more efficient way to write the query is this:


select p.PREJEMNIK, 
	SUM (case when DATUM >= '20130101' and DATUM < '20130201' THEN p.ZAPLACILO ELSE 0 END) as SumaJanuar, 
         SUM (case when DATUM >= '20130201' and DATUM < '20130301' THEN p.ZAPLACILO ELSE 0 END) as SumaFebruary,
         SUM (case when DATUM >= '20130301' and DATUM < '20130401' THEN p.ZAPLACILO ELSE 0 END) as SumaMarch,
         SUM (case when DATUM >= '20130101' and DATUM < '20130401' THEN p.ZAPLACILO ELSE 0 END) as SumaTotal
	from 
PROMET p 
join SUBJEKT s
on s.NAZIV=p.PREJEMNIK
where DATUM >= '20130101' and DATUM < '20130401' AND p.POSLDOG='3000'
group by p.PREJEMNIK
order by p.PREJEMNIK

Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 04/25/2013 :  15:59:25  Show Profile  Reply with Quote
Yap that is it :D Thanks guys!!!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.08 seconds. Powered By: Snitz Forums 2000