Counting DIFFERENT values in a MySQL table

Forum Moderators: open

Message Too Old, No Replies

Counting DIFFERENT values in a MySQL table

Spiceydog

11:13 pm on Jul 6, 2008 (gmt 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.

dreamcatcher

7:53 am on Jul 7, 2008 (gmt 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?

Spiceydog

11:22 pm on Jul 8, 2008 (gmt 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]

eelixduppy

11:30 pm on Jul 8, 2008 (gmt 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";