MySQL Query to return unique values based on distinct on 3 fields

Question

I am trying to search 1 table in the database and count the number of unique records where 3 fields do not match. I have the below but it doesn't work at all. I am an SQL noob so any help really is appreciated!

This is what I have so far

<? php
SELECT COUNT(*) 
FROM (
SELECT  DISTINCT field1, field2, field3
FROM table1);
$result = $query;
$row = mysql_fetch_array($result);
echo $row;
?>

Thanks for any help!

EDIT: I dont think this syntax does what I need it to.

I need to count the unique records in "table 1" on the basis that the fields "title","firstname", "surname" do not match another rows contents. For example the table below

+---------+-----------+-----------+-----------+
|   ID    |   Title   | Firstname | Surname   |
+---------+-----------+-----------+-----------+
|    1    |    Mr     |    J      |   Doe     |
|    2    |    Mrs    |    J      |   Doe     |
|    3    |    Mr     |    A      |   James   |
|    4    |    Mr     |    J      |   Doe     |
+---------+-----------+-----------+-----------+

The query would need to return the answer 3. There is only 1 row in the table above where "title", "firstname" & "surname" match and therefore does not get counted.

I hope that is a little clearer. I think I must be confused about what DISTINCT does!

EDIT AGAIN:

The "real world" scenario is I have a table with peoples details and want to extract them to send mail out but I do not want duplicates.

Answer 1

<?php
    $query = "select distinct field1, field2, field3 from table1"; 
    $results = mysqli_query($link, $query);    
    echo $results->num_rows;
?>

But to answer your question in SQL only,

select count(*) from (select distinct field1, field2, field3 from table1) as x;

because "every derived table must have its own alias."

Update: your understanding of distinct is accurate, actually. You can also use group by to do similar work, but group by is used for aggregating by columns, like this:

select count(*) as count, city, state from airports group by city, state;

this will give you a row for each unique city, state combination, and a single column for the number of occurrences of each, e.g.

count    city     state
--------|--------|---------|
3        Boise    ID
1        Marion   OH
24       Chicago  IL
2        Newark   DE
1        Corbin   KY
1        Ames     IA
2        Stuart   FL
...

but if you wanted just to get the count of the rows returned from that query, you'd do it a lot like you did, with a subselect:

select count(*) as rows from (select count(*) from airports group by city, state) as x;

rows
--------
2324

but there would be no reason for the overhead of doing the aggregation in the subselect. According to this guy, the two "generate the same query plan."

UPDATE 2: So the critical issue with your question is that you've asked for a solution to a problem you don't really have. Counting the rows will not help you send mail to anyone.

So what you probably want to think about is what level of "sameness" you're looking to filter out. Imagine your rows look like this:

Piet, J, Mondrian, 123 Main St, Columbus, Ohio, 43209
P, NULL, Mondrian, 123 Main St Apt. 3, Columbus, Ohio, 43209
P, Jan, Mondrian, 123 Main Street #3, Bexley, Ohio 43209

You'd probably be correct to assume this is the same person in all three cases, but which address is the best one? Your query above would send to all three. But if you group by last name and zip code, this would provide you only one of the three above. Is that the right answer? No. It's only right for those three. If your data is dirty, (which it is, if you have duplicates in it), only you know the right answer, and it depends on how dirty it is and how it got that way. But I can tell you this much: either you are going to risk sending some duplicates or you are going to spend a lot of time combing through dirty data. You have to choose which is a higher priority, your time or your appearance, because there isn't a one-size-fits-all query that's going to solve this problem. In order to create such a query, you'd need to manually go through the data to find out what the problem is. Obviously, if you did that you might as well have filtered it in Excel or something like that.

Answer 2

I'm not sure about your PHP code but the SQL query is correct (needs only an alias):

SELECT COUNT(*) 
FROM
  ( SELECT DISTINCT field1, field2, field3
    FROM table1
  ) AS x ;                                 -- you need to alias the derived table

You could also use this:

SELECT COUNT(*) 
FROM
  ( SELECT 1                                -- doesn't matter what is here
    FROM table1
    GROUP BY field1, field2, field3
  ) AS x ; 

or this query:

SELECT COUNT(DISTINCT field1, field2, field3) 
FROM table1 ; 

Answer 3

I think this is what you want?

SELECT COUNT(*) FROM table1 WHERE field1 != field2 AND field2 != field3 AND field3 != field1;

EDIT: Oh wait, this is what you want:

SELECT * FROM People GROUP BY title, firstname, surname

You can play around with it here: http://sqlfiddle.com/#!2/f489e2/3

Answer 4

Here's a clue - both in terms of how to ask a question on SO, and how to figure out the answer to this one...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID    INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,Title   VARCHAR(12) NOT NULL
,Firstname CHAR(1) NOT NULL 
,Surname  VARCHAR(12) NOT NULL 
);

INSERT INTO my_table VALUES
(1,'Mr', 'J','Doe'),
(2,'Mrs','J','Doe'),
(3,'Mr', 'A','James'),
(4,'Mr', 'J','Doe');

You give two, contradictory, requirements...

First, the thing that DISTINCT can do...

SELECT x.*
     , y.id 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.id <x.id 
   AND y.title = x.title 
   AND y.firstname = x.firstname 
   AND y.surname = x.surname;
+----+-------+-----------+---------+------+
| ID | Title | Firstname | Surname | id   |
+----+-------+-----------+---------+------+
|  1 | Mr    | J         | Doe     | NULL |
|  2 | Mrs   | J         | Doe     | NULL |
|  3 | Mr    | A         | James   | NULL |
|  4 | Mr    | J         | Doe     |    1 |
+----+-------+-----------+---------+------+

Second, the thing that DISTINCT cannot do...

SELECT x.*
     , y.id 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.id <> x.id 
   AND y.title = x.title 
   AND y.firstname = x.firstname 
   AND y.surname = x.surname;
+----+-------+-----------+---------+------+
| ID | Title | Firstname | Surname | id   |
+----+-------+-----------+---------+------+
|  1 | Mr    | J         | Doe     |    4 |
|  2 | Mrs   | J         | Doe     | NULL |
|  3 | Mr    | A         | James   | NULL |
|  4 | Mr    | J         | Doe     |    1 |
+----+-------+-----------+---------+------+