PHP Counting

[CanMan2004]

Hi all

I have a sql table which looks like

ID    NAME    USERID
1    Dave      4324566
2    John      1245690
3    Sarah    7896645
4    Dave      4324566
5    Henry    4324566
6    Sarah    1245690
7    Bill          4324566
8    Dave      8754446

What I want to do is to perform a query which will return a list of names with the most popular at the top with the least most popular at the bottom.

The twist is that if a user has added the same name more than once, then it should count it as one, for example, if it was counting the name Dave and the following was stored

ID    NAME    USERID
1    Dave      1245690
2    Dave      4324566
3    Dave      1245690

Then it would count only 2 rows, as the NAME Dave was entered by USERID 1245690 twice.

With the following

ID    NAME    USERID
1    Dave      1245690
2    Dave      4324566
3    Dave      1245690
4    Sarah      3245565

It would return

Dave - 2
Sarah - 1

Does this make sense? I have been racking my brain all day to perform this sort of query, but am totally stuck.

Any help would be ace

Thanks

Ed

[freeloader]

What do you mean by 'the most popular'?

[Psycho]

SELECT temp.name, COUNT(temp.name) as count
FROM (SELECT DISTINCT name, userid FROM `tableName`) as temp
GROUP BY name

[CanMan2004]

Thanks, that's great.

Is it possible to count the total rows in the database but if a row has the same "NAME" and "USERID" then it would count that as one row

[CanMan2004]

For example, if the database looked like

ID  NAME  USERID
1    Dave    112
2    Sarah  232
3    Bill      876
4    Dave    235
5    Sarah  232

It would count 4 rows as a total, this is because row 2 and row 5 have the same NAME and USERID.

Any help would be great

[Psycho]

Basically the same query as before w/o the GROUP BY clause (not tested):

SELECT COUNT(temp.*) as count
FROM (SELECT DISTINCT name, userid FROM `tableName`) as temp

By the way, is there any reason you can't just delete the duplicates from the table and add some logic to prevent duplicates in the future. That seems a better approach to me.

[CanMan2004]

Hi all

I get an error when I use

[code]SELECT COUNT(temp.*) as count FROM (SELECT DISTINCT name, userid FROM users) as temp[/code]

Does anyone know why?

[CanMan2004]

Can anyone help?

[mjlogan]

SELECT COUNT(temp.name) as count FROM (SELECT DISTINCT name, userid FROM users) as temp ???