Click here to Skip to main content
15,868,069 members
Home / Discussions / Database
   

Database

 
Questioncursor goes in to infinit loop......... Pin
pranavcool9-Apr-09 0:28
pranavcool9-Apr-09 0:28 
AnswerRe: cursor goes in to infinit loop......... Pin
Rob Philpott9-Apr-09 0:56
Rob Philpott9-Apr-09 0:56 
QuestionCan Predictive Analysis be done in SQL Server 2005? Pin
sunit_828-Apr-09 23:47
sunit_828-Apr-09 23:47 
QuestionHow to intergrate 2 table Pin
cocoonwls8-Apr-09 18:28
cocoonwls8-Apr-09 18:28 
AnswerRe: How to intergrate 2 table Pin
Kevin Horgan8-Apr-09 20:39
Kevin Horgan8-Apr-09 20:39 
GeneralRe: How to intergrate 2 table Pin
cocoonwls8-Apr-09 21:32
cocoonwls8-Apr-09 21:32 
GeneralRe: How to intergrate 2 table Pin
cocoonwls8-Apr-09 21:33
cocoonwls8-Apr-09 21:33 
GeneralRe: How to intergrate 2 table Pin
Kevin Horgan9-Apr-09 8:02
Kevin Horgan9-Apr-09 8:02 
Hi Cocoonwis,

You need to change the GROUP BY clause so it does not include the Amount fields.

Try this instead...

SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
group by t1.AID,t1.CODE

Good luck,

Kevin

On Apr 9, 2009, at 9:17 AM, The Code Project forums wrote:


Hi Kevin,

Thanks for your help.I got it right now Smile | :)
But i have another question about it, if the records are duplicated in t1, and also t2. How could i sum the amount in t1.Amount and t2.Amount. Example there are 2 record A1 in t1, then i would like to sum them.Also in t2, i would like to sum the amount which have the same id.

I have try in my database, it dosen't SUM for me if i write like :

SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
group by t1.AID,t1.CODE,t1.Amount,t2.Amount

thanks in advance
cocoonwls
GeneralRe: How to intergrate 2 table Pin
cocoonwls10-Apr-09 0:34
cocoonwls10-Apr-09 0:34 
GeneralRe: How to intergrate 2 table Pin
Kevin Horgan10-Apr-09 1:05
Kevin Horgan10-Apr-09 1:05 
QuestionHow to create encrypted trigger Pin
azad yadav8-Apr-09 17:51
azad yadav8-Apr-09 17:51 
AnswerRe: How to create encrypted trigger Pin
Henry Minute11-Apr-09 2:57
Henry Minute11-Apr-09 2:57 
NewsSQL Server 2008 SP1 Released Pin
brucedkyle8-Apr-09 12:21
brucedkyle8-Apr-09 12:21 
QuestionMicrosoft Access and Record Level Locking [modified] Pin
paas8-Apr-09 10:25
paas8-Apr-09 10:25 
AnswerRe: Microsoft Access and Record Level Locking Pin
Mycroft Holmes8-Apr-09 17:01
professionalMycroft Holmes8-Apr-09 17:01 
GeneralRe: Microsoft Access and Record Level Locking Pin
paas9-Apr-09 1:08
paas9-Apr-09 1:08 
GeneralRe: Microsoft Access and Record Level Locking Pin
Mycroft Holmes9-Apr-09 1:48
professionalMycroft Holmes9-Apr-09 1:48 
GeneralRe: Microsoft Access and Record Level Locking Pin
paas9-Apr-09 2:59
paas9-Apr-09 2:59 
AnswerRe: Microsoft Access and Record Level Locking Pin
Luc Pattyn8-Apr-09 17:41
sitebuilderLuc Pattyn8-Apr-09 17:41 
GeneralRe: Microsoft Access and Record Level Locking Pin
paas9-Apr-09 1:14
paas9-Apr-09 1:14 
QuestionRun Store Procedure Same Time Pin
dataminers8-Apr-09 6:02
dataminers8-Apr-09 6:02 
AnswerRe: Run Store Procedure Same Time Pin
Rob Philpott8-Apr-09 6:59
Rob Philpott8-Apr-09 6:59 
Questionhow to hide trigger so that othe user cant not modify or view Pin
azad yadav8-Apr-09 0:56
azad yadav8-Apr-09 0:56 
AnswerRe: how to hide trigger so that othe user cant not modify or view Pin
anup keshari8-Apr-09 3:31
anup keshari8-Apr-09 3:31 
GeneralRe: how to hide trigger so that othe user cant not modify or view Pin
anup keshari8-Apr-09 3:50
anup keshari8-Apr-09 3:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.