Jump to content

small COUNT(*) question


Gaia

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?

Link to comment
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?

Link to comment
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--]

Link to comment
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--]

Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.