Jump to content


Photo

Multiple count inside one query


  • Please log in to reply
4 replies to this topic

#1 webwired

webwired
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 23 March 2006 - 10:39 PM

Hi, does anyone know if it is possible to do a multiple count inside one query? Like say you want to separately count 3 different fields. The first one would be easy, to count the total, use mysql_num_rows, but the other two I'm not sure... like how many of those rows match this fields criteria and how many rows meet that fields criteria.

#2 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 24 March 2006 - 07:02 PM

[!--quoteo(post=357784:date=Mar 23 2006, 10:39 PM:name=webwired)--][div class=\'quotetop\']QUOTE(webwired @ Mar 23 2006, 10:39 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Hi, does anyone know if it is possible to do a multiple count inside one query? Like say you want to separately count 3 different fields. The first one would be easy, to count the total, use mysql_num_rows, but the other two I'm not sure... like how many of those rows match this fields criteria and how many rows meet that fields criteria.
[/quote]

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(1fieldName), COUNT(2fieldName), COUNT(3fieldName) FROM tblName [!--sql2--][/div][!--sql3--]

They're all going to, actually, be the same. SO.

Really what you're looking for.. Is one query, with 2 conditions based on other fields right?

You need to explain your question a bit more [=

Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/


#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 March 2006 - 09:02 PM

[!--quoteo(post=358012:date=Mar 24 2006, 07:02 PM:name=keeB)--][div class=\'quotetop\']QUOTE(keeB @ Mar 24 2006, 07:02 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(1fieldName), COUNT(2fieldName), COUNT(3fieldName) FROM tblName [!--sql2--][/div][!--sql3--]

They're all going to, actually, be the same. SO.
[/quote]

Not necessarily. COUNT(colname) only counts non-null values.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 webwired

webwired
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 24 March 2006 - 10:17 PM

Thank you, but actually what I was thinking and what I was typing didn't come out the same... What I really meant to ask is take for instance you have a field called link and it records hits from different advertisers that you use... i.e. advertising company 1 you give them your URL [a href=\"http://url.com/index.php?link=advertiser1\" target=\"_blank\"]http://url.com/index.php?link=advertiser1[/a] So and so forth... So now you want to do a query to get the total number of hits, the total numbers of those that are from advertiser1 and the total number of hits from advertiser2. I'll show the code I'm using right now to accomplish this task,... Now don't laugh.

$bdate = date('Y-m-d 00:00:00', mktime(0, 0, 0, $_POST[month], $_POST[day],  $_POST[year]));
$edate = date('Y-m-d 23:59:59', mktime(0, 0, 0, $_POST[month], $_POST[day],  $_POST[year]));
$query1 = mysql_query("select link from refererlog  WHERE datetime BETWEEN '$bdate' AND '$edate'") or die(mysql_error());
$row = mysql_fetch_assoc($query1);
$total = mysql_num_rows($query1);
$query2 = mysql_query("select link from refererlog where link = 'advertiser1'" AND datetime BETWEEN '$bdate' AND '$edate') or die(mysql_error());
$row = mysql_fetch_assoc($query2);
$advertiser1= mysql_num_rows($query2);
$percentadvertiser1 = (($advertiser1 / $total) * 100);
$query3 = mysql_query("select link from refererlog where link = 'advertiser2' AND datetime BETWEEN '$bdate' AND '$edate'") or die(mysql_error());
$row = mysql_fetch_assoc($query3);
$advertiser2 = mysql_num_rows($query3);
$percentadvertiser2 = (($advertiser2 / $total) * 100);
$leftover = ($total - ($advertiser1 + $advertiser2));
$percentleftover = (($leftover / $total) * 100);


#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 March 2006 - 11:40 PM

If you just want a record count it's far mor effecient to use COUNT() than to fetch the data then get the row count.

$res = mysql_query("SELECT COUNT(*) FROM refererlog  WHERE datetime BETWEEN '$bdate' AND '$edate'");
$total = mysql_result ($res, 0);

For the other counts

$query2 = mysql_query("select link, COUNT(*) as linkcount 
     FROM refererlog 
     WHERE link IN ('advertiser1', 'advertiser2') 
     AND datetime BETWEEN '$bdate' AND '$edate'
     GROUP BY link");

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users