Jump to content


Photo

Counting from two fields


  • Please log in to reply
4 replies to this topic

#1 ViperSBT

ViperSBT
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts

Posted 13 July 2006 - 06:19 PM

I have two fields in a table that both may contain a userid.  I would like to find the number of rows for each userid occurance, no matter which field it is in....  Something like:

So if the two fields were user1 and user2 and the data looked like this:

user1  user2
  2        4
  1        2
  3        5
  1        2

I would want my result to be:

1 = 2 Counts
2 = 3 Counts
3 = 1 Counts
4 = 1 Counts
5 = 1 Counts

#2 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 13 July 2006 - 06:28 PM

try this:
SELECT (
SELECT count( * )
FROM tablename
WHERE userida = '1'
) AS user1, (

SELECT count( * )
FROM tablename
WHERE useridb = '1'
) AS user2


#3 ViperSBT

ViperSBT
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts

Posted 13 July 2006 - 06:49 PM

That would require that I provide the UserID to count for, I want it to return all UserIDs in the table.  Also this provides two separate columns for each UserID, I need one column that is the UserID and another column that is the counts for the number of times that UserID exists in either of the fields of that table.

#4 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 13 July 2006 - 07:10 PM

Try this:
<?php
$temp = array();
$q = "select user1, user2 from tablename"
$rs = mysql_query($q) or die("Problem with query: $q<br>" . mysql_error());
while($rw = mysql_fetch_assoc($rs)) {
    $temp[] = $rw['user1'];
    $temp[] = $rw['user2'];
}
$nw = array_count_values($temp);
echo '<pre>' . print_r($nw,true) . '</pre>';
?>

Ken

#5 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 13 July 2006 - 07:14 PM

my bad
try this then
SELECT u.userid, (

SELECT count( * )
FROM tablename
WHERE userida  = u.userid 
) + (
SELECT count( * )
FROM tablename
WHERE useridb  = u.userid ) AS count
FROM usertable u





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users