inner join issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

inner join issue

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-04 : 08:59:40

Here's my statement:

SELECT TOP 100 PERCENT dbo.disputeattributes3.calldate, dbo.disputeattributes3.program, dbo.disputeattributes3.lob, dbo.disputeattributes3.attributes,
dbo.disputeattributes3.Disputes, dbo.disputeattributes2.sumdisputes,
dbo.disputeattributes3.Disputes * 1.0 / dbo.disputeattributes2.sumdisputes * 1.0 AS disputerate
FROM dbo.disputeattributes3 INNER JOIN
dbo.disputeattributes2 ON dbo.disputeattributes3.lob = dbo.disputeattributes2.lob
ORDER BY dbo.disputeattributes3.program, dbo.disputeattributes3.lob, disputerate DESC

Here's the results:

calldate program lob attributes Disputes sumdisputes disputerate
3/12/2009 23:59 Voyager 47. WU refund reference number 1 17 0.058823529
12/25/2008 23:59 Voyager 48. TC string information 1 17 0.058823529
2/17/2009 23:59 Voyager 48. TC string information 1 17 0.058823529
Voyager 49. Refund arrangement screens (PF15 / PF4) 1 17 0.058823529
12/25/2008 23:59 Voyager 49. Refund arrangement screens (PF15 / PF4) 1 17 0.058823529
3/1/2009 23:59 Voyager 49. Refund arrangement screens (PF15 / PF4) 1 17 0.058823529

I want attributes to be distinct. Is that possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:01:48

its possible. when you make attributes distinct, you can retrieve only single value for all the other associated fields. in that case, what should be value you would be insterested in?

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-04 : 09:03:27

For example:

48. TC string information should only be 1 and it's sum disputes is = 2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:16:08

then GROUP BY attributes field and apply aggregation (SUM) over sumdisputes field

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-04 : 09:17:47

it's not possible

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:21:40

quote:
Originally posted by BankOfficerHere

it's not possible

did you even have a try? ITS VERY MUCH POSSIBLE
and if you still its not..then post what you're exactly looking for in below format. we dont have ability to read your mind or see your system.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-04 : 09:25:13

yes i tried, i'm getting aggregate function error message..I think the system want all to use group by

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-04 : 09:27:07

Please post your query here. What fields did you group by?

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-04 : 09:28:26

SELECT TOP 100 PERCENT dbo.disputeattributes3.program, dbo.disputeattributes3.lob, dbo.disputeattributes3.attributes, dbo.disputeattributes3.Disputes,
dbo.disputeattributes2.sumdisputes, dbo.disputeattributes3.Disputes * 1.0 / dbo.disputeattributes2.sumdisputes * 1.0 AS disputerate,
dbo.disputeattributes3.calldate
FROM dbo.disputeattributes3 INNER JOIN
dbo.disputeattributes2 ON dbo.disputeattributes3.lob = dbo.disputeattributes2.lob
GROUP BY dbo.disputeattributes3.Disputes, dbo.disputeattributes3.program, dbo.disputeattributes3.lob, dbo.disputeattributes3.attributes,
dbo.disputeattributes3.Disputes, dbo.disputeattributes2.sumdisputes, dbo.disputeattributes3.calldate
ORDER BY dbo.disputeattributes3.program, dbo.disputeattributes3.lob, disputerate DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:30:30

quote:
Originally posted by BankOfficerHere

SELECT TOP 100 PERCENT dbo.disputeattributes3.program, dbo.disputeattributes3.lob, dbo.disputeattributes3.attributes, dbo.disputeattributes3.Disputes,
dbo.disputeattributes2.sumdisputes, dbo.disputeattributes3.Disputes * 1.0 / dbo.disputeattributes2.sumdisputes * 1.0 AS disputerate,
dbo.disputeattributes3.calldate
FROM dbo.disputeattributes3 INNER JOIN
dbo.disputeattributes2 ON dbo.disputeattributes3.lob = dbo.disputeattributes2.lob
GROUP BY dbo.disputeattributes3.Disputes, dbo.disputeattributes3.program, dbo.disputeattributes3.lob, dbo.disputeattributes3.attributes,
dbo.disputeattributes3.Disputes, dbo.disputeattributes2.sumdisputes, dbo.disputeattributes3.calldate
ORDER BY dbo.disputeattributes3.program, dbo.disputeattributes3.lob, disputerate DESC

what you need is to use only GROUP BY over attributes field and apply aggregation over all others. did you now understood what i asked in my first reply?

when you make attributes distinct, you can retrieve only single value for all the other associated fields. in that case, what should be value you would be insterested in?

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-04 : 09:32:11

is it possible not use aggretion? because it's really alot and keeps adding up

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:35:36