self join - 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

self join

Author

Topic

vasu4us
Posting Yak Master

102 Posts

Posted - 2006-08-09 : 15:16:07

i have 3 columns in a table

column

id type cost

1 Null 50
2 aa 30
2 ab 30
3 ac 20
4 Null 30
4 ad 30
5 ae 20
6 af 30
7 ag 50
7 ah 50

for each id cost is same so when i try to sum up the cost its adding the repeated once to
i want to count uniques id and the cost for each.
cost is something thats payed only once per id but its shown as many times the id is refered.the type column cannot be used to group as it contains null values.

thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-09 : 15:24:09

Try :


Select Q.ID, Sum(Q.Cost) from
(Select Distinct ID, Cost from UrTbl) as Q
Group By Q.ID

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-09 : 15:24:39

Could you show us what your expected result set would look like using this sample data?

Tara Kizer

vasu4us
Posting Yak Master

102 Posts

Posted - 2006-08-09 : 16:00:24

id type cost

1 Null 50
2 aa 30
3 ac 20
4 Null 30
5 ae 20
6 af 30
7 ag 50

this is what i want in the result, 1 row of data for each id.
my actual requirment is
i have a table with leadkey(identity primary),leadid,leadcost,loanid.....80 columns
each leadid has lead cost but leadid is not unique in the table so if iam trying to sum the cost iam getting the result which includes the repeated leadids
rigt now iam trying to creat a temp table with unique leadids and lead cost.iam not able to get to a conclution on this

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-09 : 16:07:45

SELECT id, MAX(type), MAX(cost)
FROM YourTable
GROUP BY id

Tara Kizer

vasu4us
Posting Yak Master

102 Posts

Posted - 2006-08-09 : 21:29:07

i was all the time trying to by just saying max(c) and was grouping other 2

it worked your way thanks TKIZER

select max(a),b,Max(c) from leadloan
where bis not null
group by b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources