Welcome to WebmasterWorld Guest from 34.204.173.45

Forum Moderators: open

Message Too Old, No Replies

Counting DIFFERENT values in a MySQL table

     
11:13 pm on Jul 6, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:May 7, 2008
posts:45
votes: 0


I have figured out how to get MySQL to count how many rows there are with the SAME name in a specific value but I need it to count the number of DIFFERENT values there are in a specific value. For instance my current code is:
$query = "SELECT user, count(albumname)
FROM albuminfo
GROUP BY user
ORDER BY albumname";

$result = mysql_query($query) or die("Couldn't execute query because: ".mysql_error());
while ($data = mysql_fetch_array($result)){

$posts = $data['COUNT(albumname)'];
}
{
echo "$user has $posts post(s)";
}

The problem is that I have 6 lines in the database that have the same value for "albumname" and of course they are counted seperately. What I want is for the script to recognize that all 6 lines are exactly the same and only read them as 1.

7:53 am on July 7, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 30, 2003
posts:3719
votes: 0


$query = "SELECT DISTINCT(albumname), user
FROM albuminfo
GROUP BY 1
ORDER BY 2";

I think something like that should work if I`m reading you correctly?

dc

11:22 pm on July 8, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:May 7, 2008
posts:45
votes: 0


Thanks! Ok so I have changed the code to this:

$query = "SELECT user, COUNT(DISTINCT(albumname))
FROM albuminfo
ORDER BY albumname";

$result = mysql_query($query) or die("Couldn't execute query because: ".mysql_error());
while ($data = mysql_fetch_array($result)){

$posts = $data['COUNT(DISTINCT(albumname))'];
}
{
echo "$user has $posts post(s)";
}

And now I am getting this error:
Couldn't execute query because: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

HELP!

[edited by: Spiceydog at 11:22 pm (utc) on July 8, 2008]

11:30 pm on July 8, 2008 (gmt 0)

Senior Member

joined:Nov 12, 2005
posts:5967
votes: 0


Try this query. I'm making some assumptions here but it might work as you want:

$query = "SELECT user, COUNT(user) FROM albuminfo GROUP BY albumname ORDER BY albumname";