Home » SQL & PL/SQL » SQL & PL/SQL » mutiple adress intable
mutiple adress intable [message #305586] Tue, 11 March 2008 05:48 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member


HI all,

i have table A  it has mutiple adress how can count mutiple adress

A
1
1
1
3
3
2
5
5
5

i want expect answer count is  5

because  1 has    2 mutiple records.
         3  has   1 
         2  has   0  
         5  has   2
         sum is  :5     

thanks
srinivas











Re: mutiple adress intable [message #305588 is a reply to message #305586] Tue, 11 March 2008 05:53 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
242 posts and you still ask questions this way, unbelievable.
Post a test case.
Re: mutiple adress intable [message #305590 is a reply to message #305588] Tue, 11 March 2008 05:54 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
yes i got it

select sum(mutiadress) from
(

select count(*) -1 as mutiadress, STRATEGY from au_order_dtl group by STRATEGY

having count(*) > 1
)
Re: mutiple adress intable [message #305593 is a reply to message #305586] Tue, 11 March 2008 05:54 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
can you explain how
Quote:
because 1 has 2 mutiple records.
3 has 1
2 has 0
5 has 2

i can see count of 1 as 3 and so on.


regards
Re: mutiple adress intable [message #305595 is a reply to message #305586] Tue, 11 March 2008 05:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you need
HAVING COUNT(*) > 1
It doesn't improve anything (addresses with only one occurence will still have "1 - 1 = 0".
Re: mutiple adress intable [message #305603 is a reply to message #305595] Tue, 11 March 2008 06:15 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
yes

adress
1
2
2
3
3
4
4
5
....
count=3
Re: mutiple adress intable [message #305604 is a reply to message #305603] Tue, 11 March 2008 06:16 Go to previous message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

yes it is working fine

select sum(mutiadress) from
(

select count(*) -1 as mutiadress, STRATEGY from au_order_dtl group by STRATEGY

having count(*) > 1
)
Previous Topic: Error: String literal too long
Next Topic: PL/SQL
Goto Forum:
  


Current Time: Fri Dec 09 13:16:47 CST 2016

Total time taken to generate the page: 0.13972 seconds