count() result query

[annan]

Hello frendz


I'm with version 4.0.27. My query is suppose to display 11 records, but when I join two tables, the result is much more than it supposed to be.

Code:
SELECT t1.f1, t2.f1, count( t1.f1 ) AS totalc
FROM USA
LEFT JOIN CANADA ON t1.f1 = t2.f1
WHERE t1.f2 = '1'
GROUP BY t1.f1
ORDER BY 'totalc' DESC

Thanks in advance for your replies -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[tyrisha]

You need to group by all non-aggregate fields, or else you'll get unpredictable results:
code;

SELECT
t1.f1
, t2.f1
, COUNT( t1.f1 ) AS totalc
FROM USA
LEFT JOIN CANADA ON t1.f1 = t2.f1
WHERE t1.f2 = '1'
GROUP BY
t1.f1
, t2.f1
ORDER BY
totalc
DESC

SELECT t1.f1, t2.f1, COUNT( t1.f1 ) AS totalcFROM USA LEFT JOIN CANADA ON t1.f1 = t2.f1 WHERE t1.f2 = '1'GROUP BY t1.f1 , t2.f1 ORDER BY totalc DESC -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[algo]

What tyrisha said is fine, you should always use correct GROUP BY syntax

however, in this case it will not solve the problem because t1.f1 = t2.f1

also, your original query as given won't run, because t1 and t2 are not defined

assuming that USA is t1 and CANADA is t2, try this --
Code:
SELECT t1.f1
, t2.f1
, ( select count(*)
from USA
where f1 = '1' ) AS totalc
FROM USA as t1
LEFT
JOIN CANADA as t2
ON t2.f1 = t1.f1
WHERE t1.f2 = '1'
ORDER
BY totalc DESCnote also that the ORDER BY is a column, not a string

cheers -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --