CODEWITHSUNDEEP
phpsqlcount
Max
Max

Reputation: 1213

SELECT COUNT(DISTINCT name), id, adress from users

With PHP I'm trying to run a SQL query and select normal columns as well as COUNT.

$sql_str =  "select COUNT(DISTINCT name), id, adress from users";
$src = mysql_query($sql_str);

while( $dsatz = mysql_fetch_assoc($src) ){
    echo $dsatz['name'] . "<br>";
}

The problem is that when I have "COUNT(DISTINCT name)," in my query, it will only return the first entry. When I remove it, it will return all matching entries from the db.

I could separate it and do 2 queries, but I'm trying to avoid this due to performance concerns.

What do I make wrong? thx, Mexx

Upvotes: 1

Views: 14063

Answers (6)

user30932
user30932

Reputation:

The ability to mix normal columns and aggregate functions is a (mis)feature of MySQL. You can even read why it's so dangerous on MySQL's documentation: https://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html

But if you really want to mix normal rows and a summary in a single query, you can always use the UNION statement:

SELECT COUNT(DISTINCT name), null, null FROM users GROUP BY name --summary row
UNION
SELECT name, id, address FROM users --normal rows

Upvotes: 4

Sudhir Jonathan
Sudhir Jonathan

Reputation: 17526

I'm assuming you want to get all your users and the total count in the same query.

Try 

  select name, id, address, count(id) as total_number 
    from users 
    group by name, id, address;

Upvotes: 0

Ryan Liang
Ryan Liang

Reputation: 668

select count(distinct name), id, adress 
from users 
group by id, adress

Upvotes: 0

Raj
Raj

Reputation: 1770

When you have a count() as part of the field list you should group the rest of the fields. In your case that would be 

select count(distinct name), id, adress from users group by id, adress

Upvotes: 0

Pascal MARTIN
Pascal MARTIN

Reputation: 401172

From what I understand, you want to get :

  • one line per user, to get each name/id/address
  • one line for several users at the same time, to get the number of users who have the same name.

This is not quite possible, I'd say.


A solution would be, like you said, two queries...

... Or, in your case, you could do the count on the PHP side, I suppose.
ie, not count in the query, but use an additionnal loop in your PHP code.

Upvotes: 0

Donnie
Donnie

Reputation: 46933

COUNT() is an aggregate function, it aggregates against the results of the rest of your query. If you want to count all distinct names, and not just the distinct names associated with the id and address that you are selecting, then yes, you will have to run two queries. That's just how SQL works.

Note that you should also have a group by clause when aggregating. I think the fact that MySQL doesn't require it is horrible, and it encourages really bad habits.

Upvotes: 2

Related Questions