SQL Duplicated names in result

Question

Hi, I've got problem with SQL. Here is my code:

SELECT Miss.Name, Miss.Surname, Master.Name, Master.Surname,
            COUNT(Date.Id_date) AS [Dates_together]
FROM Miss, Master, Date
WHERE Date.Id_miss = Miss.Id_miss AND Date.Id_master = Master.Id_master
GROUP BY Miss.Name, Miss.Surname, Master.Name, Master.Surname
ORDER BY [Dates_together] DESC

and I've got the result:

Dorothy | Mills   | James | Jackson | 28
Dorothy | Mills   | Kayne | West    | 28
Emily   | Walters | James | Jackson | 13
Emily   | Walters | Tom   | Marvel  | 12
Sunny   | Sunday  | Kayne | West    | 9

and I really do not know what to change to have a result like this:

Dorothy | Mills   | James | Jackson | 28
Emily   | Walters | Tom   | Marvel  | 12
Sunny   | Sunday  | Kayne | West    | 9

Because I don't want to to have duplicated names of master or miss in a result... :( Can anyone help me?

Answer 1

It looks like your result set is correct, as you are getting the appropriate distinct combinations.

Answer 2

Use DISTINCT operator

Without DISTINCT

SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE SalesOrderID BETWEEN 43685 AND 43687;

Result:

SalesOrderID
------------
43685
43685
43685
43685
43686
43686
43686
43687
43687  

With DISTINCT

SELECT DISTINCT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE SalesOrderID BETWEEN 43685 AND 43687;

Result:

SalesOrderID
------------
43685
43686
43687

FOR COMMENTATORS: Look at what he wants!!! He wants to leave only first entry of duplicate items.

Answer 3

The "duplicates" are accurate, because you are querying the combinations of the Miss and Master records, not the Miss and Master records themselves. For instance, in your second result set, it doesn't capture the fact that Dorothy Mills dated Kayne West 28 times.

Answer 4

Hi Agnes,

You don't mention which database you're working with, but if I have this correctly you're trying to determine how many times a given couple have been on a date?

I think you need to ask your self what happens if you have two people, of either sex, that share the same combination of christian and surname...

Start off with :

Select idMaster, idMiss, count(*) as datecount from [Date] group by idMaster, idDate

From there, you need to simply need to add their names to the results...

Should get you started on the right track...