Unique value - 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

Unique value

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2008-06-28 : 03:55:29

I am using Distinct keyword, but it doesn't help me to give Unique info.
Query is:-

select distinct m.aid,m.cstatus as AssociateID,sum(p.fpay) as payment,
(sum(l.lvl1)+sum(l.lvl2)+sum(l.lvl3)+sum(l.lvl4)+sum(l.lvl5)) as levelpay
from members m join payment p
on m.cstatus='V'
and m.cclosing=p.cClosingno
join levelpayment l
on m.cclosing=l.cClosingno

and m.cclosing='8'
group by m.aid,m.cstatus,p.fpay
order by m.aid asc

output comes as:

Aid Status payment levelpay
______________________________________________________________________________

U100006391 V 0 1164000
U100006391 V 10 480
U100006391 V 600 12000
U100006391 V 600 24000
U100006391 V 600 48000
U100006391 V 789 189360
U100006391 V 800 96000
U100006391 V 1200 12000

plz help me.

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 03:59:42

quote:
Originally posted by vedjha

I am using Distinct keyword, but it doesn't help me to give Unique info.
Query is:-

select distinct m.aid,m.cstatus as AssociateID,sum(p.fpay) as payment,
(sum(l.lvl1)+sum(l.lvl2)+sum(l.lvl3)+sum(l.lvl4)+sum(l.lvl5)) as levelpay
from members m join payment p
on m.cstatus='V'
and m.cclosing=p.cClosingno
join levelpayment l
on m.cclosing=l.cClosingno

and m.cclosing='8'
group by m.aid,m.cstatus,p.fpay
order by m.aid asc

output comes as:

Aid Status payment levelpay
______________________________________________________________________________

U100006391 V 0 1164000
U100006391 V 10 480
U100006391 V 600 12000
U100006391 V 600 24000
U100006391 V 600 48000
U100006391 V 789 189360
U100006391 V 800 96000
U100006391 V 1200 12000

plz help me.

Ved Prakash Jha

if you're looking for distinct values of Aid,Status then this is probably what you want, the reason you're getting duplicate values is because it will looks for distinct combination of all values given in group by ( and other field p.fpay have unique values). so change it like this & you dont even require distinct:-

select distinct m.aid,m.cstatus as AssociateID,sum(p.fpay) as payment,
(sum(l.lvl1)+sum(l.lvl2)+sum(l.lvl3)+sum(l.lvl4)+sum(l.lvl5)) as levelpay 
from members m join payment p
on  m.cstatus='V'
and m.cclosing=p.cClosingno
join levelpayment l
on m.cclosing=l.cClosingno

and m.cclosing='8'
group by m.aid,m.cstatus,p.fpay
order by m.aid asc

mr_dayal
Starting Member

37 Posts

Posted - 2008-06-28 : 04:06:24

got the answer ved?

Mr Dayal

vedjha
Posting Yak Master

228 Posts

Posted - 2008-06-28 : 04:10:59

Thank u Vikash. it works.
asking u a personal question that,
U have no any physical data but you study and give output.
How to do. will u give me trick? how tosolve without practical?
please...

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 05:03:01

quote:
Originally posted by vedjha

Thank u Vikash. it works.
asking u a personal question that,
U have no any physical data but you study and give output.
How to do. will u give me trick? how tosolve without practical?
please...

Ved Prakash Jha

Its just by experience. Most problems will be something that we might have encountered sometime during our work so that whenever we see the error it will automatically occur that this may be cause of it. Because we have experienced it one time or other berfore we will knowing how to resolve it. Thats all

. I'm sure with time every one including you will be able to identify why a particular error occured and how you can rectify that.
And always the best lesson for us is learning from our own mistakes.So what we should be trying to do is to understand why a particular error occured and how we can resolve whenever we see one in our code.Also referring to forums like these we will come across new approaches to scenario and even about new functions which we have never come across. We should try to understand them and apply it to wherever we can and by doing so our knowledge base will keep on expanding.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources