Jump to content

Archived

This topic is now archived and is closed to further replies.

Gaia

small COUNT(*) question

Recommended Posts

hey,

 

For statistics purposes on my site i use multiple COUNT(*) queries. (counting from multiple different tables).

 

I was just wondering if there was anyway that i could use the COUNT(*) only once but still get the count result from more then just one table?

Share this post


Link to post
Share on other sites

At what level of information are you looking for? If you using a newer version of MySQL you might try nested subquireis in your joins and then have each subquery use count(*), but can the information you're looking for be counted only once or does it require more than one value?

Share this post


Link to post
Share on other sites

Well this is how i have it set up now:

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]//number of forum members

$result3 = mysql_query("[span style=\'color:blue;font-weight:bold\']SELECT COUNT(*) FROM ibf_members")[/span] or die(mysql_error());

$stats[mems] = mysql_result($result3,0);

$result = mysql_query("[span style=\'color:blue;font-weight:bold\']SELECT COUNT(*) FROM ibf_posts")[/span] or die(mysql_error());

$stats[posts] = mysql_result($result,0);

 

//number of files

$result2 = mysql_query("[span style=\'color:blue;font-weight:bold\']SELECT COUNT(*) FROM downloads")[/span] or die(mysql_error());

$stats[files] = mysql_result($result2,0);

 

//Number of affiliates

$result = mysql_query("[span style=\'color:blue;font-weight:bold\']SELECT COUNT(*) FROM affiliates WHERE validated='[/span]1'") or die(mysql_error());

$stats[affs] = mysql_result($result,0);

 

//number of comments

$result2 = mysql_query("[span style=\'color:blue;font-weight:bold\']SELECT COUNT(*) FROM comments")[/span] or die(mysql_error());

$stats[comments] = mysql_result($result2,0);

 

//number of downloads

$query = mysql_query("[span style=\'color:blue;font-weight:bold\']SELECT * FROM downloads")[/span] or die(mysql_error());

while($loop = mysql_fetch_array($query)) { // This loops the data in the query, if the query is called $query

$stats[dnum] = $stats[dnum] + $loop['downloads']; //this adds em up

}

[!--sql2--][/div][!--sql3--]

Share this post


Link to post
Share on other sites

I don't really see anything wrong with the way you're doing it now, but you can do the following to simplify it

$query = '(SELECT "mems" AS t, COUNT(*) AS c FROM ibf_members) '
             .'UNION ALL '
             .'(SELECT "posts" AS t, COUNT(*) AS c FROM ibf_posts) '
             .'UNION ALL '
             .'(SELECT "files" AS t, COUNT(*) AS c FROM downloads) '
             .'UNION ALL '
             .'(SELECT "affs" AS t, COUNT(*) AS c FROM affiliates WHERE validated="1") '
             .'UNION ALL '
             .'(SELECT "comments" AS t, COUNT(*) AS c FROM comments) '
             .'UNION ALL '
             .'(SELECT "dnum" AS t, SUM(downloads) AS c FROM downloads) ';
$result = mysql_query($query) or die(mysql_error());
$stats = array();
while($row = mysql_fetch_assoc($result))
{
    $stats[($row['t'])] = $row['c'];
}
print_r($stats);

If you're using mysql 4.1 or higher you can use subqueries to get the results you're looking for in one row. Which may be faster.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] ([span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(*) FROM table) AS affs, ([span style=\'color:blue;font-weight:bold\']SELECT[/span] SUM(downloads) FROM downloads) AS dnum [!--sql2--][/div][!--sql3--]

Share this post


Link to post
Share on other sites

[!--quoteo(post=321328:date=Nov 22 2005, 10:22 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Nov 22 2005, 10:22 PM) 321328[/snapback][/div][div class=\'quotemain\'][!--quotec--]

I don't really see anything wrong with the way you're doing it now, but you can do the following to simplify it

$query = '(SELECT "mems" AS t, COUNT(*) AS c FROM ibf_members) '
             .'UNION ALL '
             .'(SELECT "posts" AS t, COUNT(*) AS c FROM ibf_posts) '
             .'UNION ALL '
             .'(SELECT "files" AS t, COUNT(*) AS c FROM downloads) '
             .'UNION ALL '
             .'(SELECT "affs" AS t, COUNT(*) AS c FROM affiliates WHERE validated="1") '
             .'UNION ALL '
             .'(SELECT "comments" AS t, COUNT(*) AS c FROM comments) '
             .'UNION ALL '
             .'(SELECT "dnum" AS t, SUM(downloads) AS c FROM downloads) ';
$result = mysql_query($query) or die(mysql_error());
$stats = array();
while($row = mysql_fetch_assoc($result))
{
    $stats[($row['t'])] = $row['c'];
}
print_r($stats);

If you're using mysql 4.1 or higher you can use subqueries to get the results you're looking for in one row. Which may be faster.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] ([span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(*) FROM table) AS affs, ([span style=\'color:blue;font-weight:bold\']SELECT[/span] SUM(downloads) FROM downloads) AS dnum [!--sql2--][/div][!--sql3--]

'comments' might be returned as 'comm' here, same for 'posts' and 'files'... the length of a field in UNION is defined by the first query... to be safe, make them all the same length, or switch them around

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.