Jump to content


Photo

small COUNT(*) question


  • Please log in to reply
4 replies to this topic

#1 Gaia

Gaia
  • Members
  • PipPipPip
  • Advanced Member
  • 95 posts
  • LocationCanada

Posted 20 November 2005 - 09:50 PM

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?

#2 sqlmc

sqlmc
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 23 November 2005 - 03:30 AM

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?

#3 Gaia

Gaia
  • Members
  • PipPipPip
  • Advanced Member
  • 95 posts
  • LocationCanada

Posted 23 November 2005 - 05:39 AM

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

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 23 November 2005 - 06:22 AM

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

#5 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 23 November 2005 - 06:57 AM

[!--quoteo(post=321328:date=Nov 22 2005, 10:22 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Nov 22 2005, 10:22 PM) View Post[/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--]
[/quote]
'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
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users