get count on selected query of count ??
 

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.

Re: get count on selected query of count ??
 

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;

Re: get count on selected query of count ??
 

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

Re: get count on selected query of count ??
 

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