Home » SQL & PL/SQL » SQL & PL/SQL » sum of the range
sum of the range [message #194279] Thu, 21 September 2006 06:54 Go to next message
speaker
Messages: 30
Registered: April 2006
Member
Hi

select count(subs_id) "Number of subscribers" , cnt "Number of Profiles"
from ( select subs_id,count(*) cnt
from subscriber_INFO
where caller_type=1
group by subs_id)
group by cnt

The output will be like this

Subcr profiles
ibers

18 1
9 2
6 3
5 4
1 5
2 8
1 10

There will be millions of subscribers and each might have upto 50 profiles or something.

so i want the output something like


Number of subscribers Range of profile [ 1-10 ]

ie... this many number of subscribers having profiles ranging 1-10


33 1-10
24 11-20
8 21-30

like this..

this has to be in a single query..

pls help
Re: sum of the range [message #194283 is a reply to message #194279] Thu, 21 September 2006 07:05 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

try this, but it is not tested

SELECT count(subs_id) "Number of subscribers",(cnt-9)||'-'||cnt "Number of Profiles" 
FROM 
(
SELECT subs_id, (ceil(count(*)/10))*10 cnt 
FROM subscriber_info 
WHERE caller_type=1 
GROUP BY subs_id
)
GROUP BY (cnt-9)||'-'||cnt;

perhaps it will work for u

** updated cnt of subquery

Sandy

[Updated on: Thu, 21 September 2006 07:27]

Report message to a moderator

Re: sum of the range [message #194287 is a reply to message #194279] Thu, 21 September 2006 07:20 Go to previous messageGo to next message
speaker
Messages: 30
Registered: April 2006
Member
No it diint serve the purpose
Re: sum of the range [message #194295 is a reply to message #194287] Thu, 21 September 2006 07:37 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

now i had tested it on my table

SQL> SELECT created_by, count(*) cnt
2 FROM ABC_CODES
3 GROUP BY created_by;


CREATED_BY CNT
------------------------------ ----------
ASOR01 1
ASOR02 22
CUOWN 3


SQL> SELECT created_by, (ceil(count(*)/10))*10 cnt
2 FROM ABC_CODES
3 GROUP BY created_by;

CREATED_BY CNT
------------------------------ ----------
ASOR01 10
ASOR02 30
CUOWN 10


SQL>SELECT count(created_by) "count_subs",(cnt-9)||'-'||cnt "count_profile"
2 FROM
3 (
4 SELECT created_by, (ceil(count(*)/10))*10 cnt
5 FROM ABC_CODES
6 GROUP BY created_by
7 )
8 GROUP BY (cnt-9)||'-'||cnt;



count_subs count_profile
---------- ---------------------------
2 1-10
1 21-30


is it write??


Sandy
Re: sum of the range [message #194297 is a reply to message #194287] Thu, 21 September 2006 07:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Go on - TRY to help us to help you.
What about @Sandeepk7s solution doesn't work for you?
Re: sum of the range [message #194299 is a reply to message #194295] Thu, 21 September 2006 07:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've tested your solution too, and it looks fine to me.
Re: sum of the range [message #194300 is a reply to message #194299] Thu, 21 September 2006 07:44 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Thanks...

Sandy Smile
Re: sum of the range [message #194406 is a reply to message #194279] Fri, 22 September 2006 00:41 Go to previous message
speaker
Messages: 30
Registered: April 2006
Member
Hi Sandy,

IT worked ...
Thanks a lot..

Previous Topic: Delta Refresh and Full Refresh?
Next Topic: SQL query not running
Goto Forum:
  


Current Time: Sat Dec 10 20:23:48 CST 2016

Total time taken to generate the page: 0.07506 seconds