get count on selected query of count ??

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 401,673 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,426 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Thread Tools Display Modes

Views: 663 | Replies: 3

•

Join Date: May 2008

Posts: 1

Reputation: navneet1083 is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

navneet1083 navneet1083 is offline

Offline

Newbie Poster

get count on selected query of count ??

May 21st, 2008

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 :

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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 
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.

Last edited by peter_budo : May 23rd, 2008 at 11:13 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: May 2008

Posts: 10

Reputation: jemajoign is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

jemajoign

Offline

Newbie Poster

Re: get count on selected query of count ??

May 21st, 2008

Is this what you need?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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;
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;

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: get count on selected query of count ??

May 21st, 2008

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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

Last edited by tesuji : May 21st, 2008 at 6:56 pm.

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: get count on selected query of count ??

May 22nd, 2008

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
----  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 
----  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 