Home » SQL & PL/SQL » SQL & PL/SQL » tricky grouping
tricky grouping [message #271754] Tue, 02 October 2007 15:46 Go to next message
techluver
Messages: 16
Registered: May 2006
Junior Member
Hello,
Please help me on this scenario.

I have ADDRESS table with following columns:
community_name,community_address,city,state

Under a community_name, multiple community_address can occur like a community can have multiple apartments.

sample records in this table can be as follows:
community_name, community_address, city, state
1.starbucks, 1 first st, denver, co
2.starbucks, 2 second st, denver, co
3.spyhill, 1st cross st, phoenix, az
4.spyhill, 2nd cross st, phonix, az

I can write following query to get the records grouped by community_name,city,state and its count.
select community_name,city,state,count(*)
from address
group by community_name,city,state

so output of this query would be
starbucks,denver,co,2
spyhill,phonix,az,2

But I also want to have community_address(any community address from the grouping) in the above result without including the community_address in group by clause.

so my expected output is
starbucks,denver,co,2, 1 first st (or 2 second st)
spyhill,phonix,az,2,1st cross st(or 2nd cross st)

I'm not sure how I can achieve this using sql or stored procedure. Kindly help me on this.
thanks in advance.
Re: tricky grouping [message #271759 is a reply to message #271754] Tue, 02 October 2007 16:33 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
But I also want to have community_address(any community address from the grouping) in the above result without including the community_address in group by clause.
So add community_address into SELECT clause processed by any reasonable aggregate function (MIN or MAX).
You will get biggest/lowest address (using string comparision rules).
Re: tricky grouping [message #271795 is a reply to message #271754] Wed, 03 October 2007 00:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If one community_name can have multiple addresses, which address would you show? A random one?
Re: tricky grouping [message #271803 is a reply to message #271754] Wed, 03 October 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: tricky grouping [message #271998 is a reply to message #271795] Wed, 03 October 2007 10:54 Go to previous messageGo to next message
techluver
Messages: 16
Registered: May 2006
Junior Member
Yeah. Random one.
Re: tricky grouping [message #273882 is a reply to message #271998] Fri, 12 October 2007 01:43 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

if the address doesn't matter for you, than you can use something as below to get your desired output.
with comm_details as 
(select 'starbucks' comm_name,'1 first st' comm_add,'denver' city,'co' state from dual union all
select 'starbucks','2 second st','denver','co' from dual union all
select 'spyhill','1st cross st','phoenix','az' from dual union all
select 'spyhill','2nd cross st','phonix','az'  from dual
)
select comm_name,comm_add,city,state,comm_count from
(select comm_name,comm_add,city,state,
count(*) over(partition by comm_name)comm_count,
row_number() over(partition by comm_name order by comm_add)r
from comm_details)
where r=1



regards,
Re: tricky grouping [message #273887 is a reply to message #273882] Fri, 12 October 2007 01:54 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
OR (after correcting the OPs spelling of Phoenix) a simpler query
with comm_details as 
(select 'starbucks' comm_name,'1 first st' comm_add,'denver' city,'co' state from dual union all
select 'starbucks','2 second st','denver','co' from dual union all
select 'spyhill','1st cross st','phoenix','az' from dual union all
select 'spyhill','2nd cross st','phoenix','az'  from dual
)
select comm_name,max(comm_add),city,state,count(*)
from comm_details
group by comm_name,city,state
Previous Topic: Diffrence in number records while using subquery
Next Topic: Multi Level Nested Tables
Goto Forum:
  


Current Time: Sun Dec 11 00:03:19 CST 2016

Total time taken to generate the page: 0.05089 seconds