CODEWITHSUNDEEP
mysqlsqldistinct
geekashu
geekashu

Reputation: 1

MySQL SELECT DISTINCT returning different results

I am not able to figure out why this is happening. Following MySQL Query return me 49 rows but "Total" in each rows is displayed as 48.

SELECT DISTINCT t2.CIN, t1.Name, 
  (SELECT COUNT(DISTINCT CIN) FROM `cSignatoryAssociations` WHERE DIN ='00016902') As Total
FROM `cSignatoryAssociations` t2
LEFT JOIN `cRoC` t1 ON t1.CIN = t2.CIN
WHERE t2.DIN='00016902'  
ORDER BY SUBSTRING(t1.CIN,9,4) DESC;

When following code is run separately i get expected results i.e 49.

SELECT COUNT(DISTINCT CIN) FROM `cSignatoryAssociations` WHERE DIN ='00016902'

Please help me figure out what is wrong.

Upvotes: 0

Views: 266

Answers (2)

echo_Me
echo_Me

Reputation: 37233

Try that:

SELECT t2.CIN, t1.Name, COUNT(t2.CIN)
FROM `cSignatoryAssociations` t2
LEFT JOIN `cRoC` t1 ON t1.CIN = t2.CIN
WHERE t2.DIN='00016902'  
GROUP BY t1.Name
ORDER BY SUBSTRING(t1.CIN,9,4) DESC;

One thing i dont know what you want do but just make sure you want LEFT JOIN or INNER JOIN. because they dont give same result.

Upvotes: 1

Baalthasarr
Baalthasarr

Reputation: 367

Like you said 

SELECT COUNT(DISTINCT CIN) FROM `cSignatoryAssociations` WHERE DIN ='00016902'

returns 49 rows. But this is a sub Select and the main Select have also a Distinct.

The Main Select with the Distinct will remove lines that are the sames and this will be the case.

The Whole Select will return 49 Rows, but there are 2 same tuple and Distinct will remove one. So the result will be 48 Rows.

Upvotes: 1

Related Questions