Hi,

SQL below returns 1, 2, 3 in three rows. I want to see only 3 because there are total 3 records.

How can I solve this problem.

Thanks

SELECT COUNT(*) as totalProduct
                FROM customers
                INNER JOIN uploads ON customers.id = uploads.fk_id
                GROUP BY name

Remove phrase "group by name" from your query.

No, in that case it returns 7 because it is a 1 to n relationship. therefore, one customer has more than one uploads.

Can I not do it without adding this bit?

ORDER BY COUNT(*) DESC
                LIMIT 1
SELECT COUNT(*) as totalProduct               
 FROM customers                
INNER JOIN uploads ON customers.id = uploads.fk_id                
where name='MyRequiredName'
GROUP BY name

Why use WHERE? I want to count all not for particular person.

If you want to count that how many customers had uploaded then use the following query

SELECT COUNT(DISTINCT fk_id) FROM uploads

I decided to add bit below because there is another JOIN tehere. It is becoming a bit complex now. Anyway, thanks for your helps guys.

ORDER BY COUNT(*) DESC                LIMIT 1
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.