CODEWITHSUNDEEP
mysqlsqlsqlite
Jürgen Paul
Jürgen Paul

Reputation: 15027

Weird issue on count and group_by and maybe distinct

Scenario:

I have a table named snippets with 5 rows, I have a table named revisions with 15 rows

What I wanted to do was get all snippets, which belongs a user_id on revisions, which includes the snippet_id.

I have the following mysql query:

SELECT DISTINCT * FROM "snippets" 
INNER JOIN "revisions" 
    ON "user_id" = "1" 
    AND "snippet_id" = "snippets.id"
GROUP BY "snippets"."id" 
ORDER BY "created_at"

That should display all snippets where revisions.user_id = 1. The problem is, why is my count different? The total results from that query is 5, but when I do:

SELECT COUNT(distinct snippets.id) FROM "snippets" 
INNER JOIN "revisions" 
    ON "user_id" = "1" 
    AND "snippet_id" = "snippets.id"
GROUP BY "snippets"."id";

The result is 1 1 1 1 1

Upvotes: 0

Views: 48

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

There is no meaning for DISTINCT COUNT(*). May be you are looking for COUNT(DISTINCT someotherfield) instead of it. Since COUNT(*) includes any rows that has NULL values.

Update:* Don't COUNT the same field that you used in the GROUP BY clause. That is why you are getting the result 1 1 1 1 1. In this case remove the GROUP BY "snippets"."id" clause.

Upvotes: 2

Related Questions