Jump to content

Archived

This topic is now archived and is closed to further replies.

ViperSBT

Counting from two fields

Recommended Posts

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

Share this post


Link to post
Share on other sites
try this:
[code]
SELECT (
SELECT count( * )
FROM tablename
WHERE userida = '1'
) AS user1, (

SELECT count( * )
FROM tablename
WHERE useridb = '1'
) AS user2
[/code]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Try this:
[code]<?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>';
?>[/code]

Ken

Share this post


Link to post
Share on other sites
my bad
try this then
[code]
SELECT u.userid, (

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

Share this post


Link to post
Share on other sites

×

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.