get count on selected query of count ??

[navneet1083]

I've fired a query which result some data with the respective count from the table and after that i want to fire a query to get the total number of count on that count which i got it from my first query. Could anyone helps me out to get the total count of the query of a query.
Suppose i've query like :

select i, substring_index(source_ip, '.', i) source_ip_new, count(*) as myCount 
from 
(select (1) as i union select (2) union select (3) union select (4)) as n 
join userbase_copy 
group by i, source_ip_new 
having i = 3 and count(*) > 0 
order by myCount desc

which will give me the count of ip(xxx.xxx.xxx.xxx) with equals ignoring the last ip index. After this i want to get count on the whole result. suppose i got result like :
=============================
3 203.88.10 4 4
3 122.166.12 3
3 203.88.8 1 1
3 117.97.13 1 1
3 117.97.37 1
==============================
After this reslut how to get count on the above result. The count should give me the value 10(4+3+1+1+1).
any suggestions/helps are welcome.
thanks in advance.

[jemajoign]

Is this what you need?

select  i, substring_index(source_ip, '.', i) source_ip_new, count(*) as myCount
from (
	select (1) as i 
	union select (2) 
	union select (3) 
	union select (4)
) as n
join userbase_copy
group by i, source_ip_new with rollup
having i = 3 and count(*) > 0;

[tesuji]

Hi navneet1083,

I am not that sure whether the following sql statement would work correctly for your problem, but I have similar problem tested with my database where I have created subtotals with with-clause first then I "unioned" this result with grand total. Maybe you can try this:

with subtotals (n, ip, x, st) as
(  
   select cast(i as char), substring_index(source_ip, '.', i), 
      source_ip_new, count(*) as myCount
   from
   (select (1) as i union select (2) union select (3) union select (4)) as n
   join userbase_copy
     group by i, source_ip_new having i = 3 and count(*) > 0
   order by myCount desc
)
select * from  subtotals
union
select n, 'Total ', 'counts', sum(st) from subtotals

I simply copied your select statement and put it into (...) without checking it. Because columns of unions must be compatible, cast(i) might be necessary.

Ok, similar construct works fine on my tables. So I hope it also does it for your problem.
Tell me whether it works on your tables, maybe also the modifications you made.

krs,
tesu

[tesuji]

----  Line 13 of previous code listing:

SELECT n, 'Total ', 'counts', sum(st) FROM subtotals

----  must be changed to:

SELECT n, 'Total ', 'counts', sum(st) FROM subtotals group by n

krs,
tesu