Member Avatar for veledrom

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
  • 3 Contributors
  • 6 Replies
  • 93 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by veledrom
Member Avatar for urtrivedi

Remove phrase "group by name" from your query.

Edited by urtrivedi because: n/a
Member Avatar for veledrom

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
Member Avatar for urtrivedi 
SELECT COUNT(*) as totalProduct               
 FROM customers                
INNER JOIN uploads ON customers.id = uploads.fk_id                
where name='MyRequiredName'
GROUP BY name
Member Avatar for veledrom

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

Member Avatar for mwasif

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

SELECT COUNT(DISTINCT fk_id) FROM uploads
Member Avatar for veledrom

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.