small COUNT(*) question

[Gaia]

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?

[sqlmc]

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?

[Gaia]

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

[shoz]

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

[ryanlwh]

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