CODEWITHSUNDEEP
mysqlcount
vephelp
vephelp

Reputation: 542

COUNT returns more values than when actual results are pulled

Query

SELECT DISTINCT walls.wall_id FROM walls 
LEFT JOIN wall_categories ON walls.wall_id = wall_categories.wall_id 
LEFT JOIN wall_devices ON walls.wall_id = wall_devices.wall_id
WHERE wall_categories.category_id IN (1,2,3)
AND wall_devices.device_id IN (1,2,3)
AND walls.wall_id>113;

Above query return the result below:

enter image description here

And if I add COUNT to the exact same query like:

SELECT DISTINCT COUNT(walls.wall_id) FROM walls 
LEFT JOIN wall_categories ON walls.wall_id = wall_categories.wall_id 
LEFT JOIN wall_devices ON walls.wall_id = wall_devices.wall_id
WHERE wall_categories.category_id IN (1,2,3)
AND wall_devices.device_id IN (1,2,3)
AND walls.wall_id>113;

It returns = 14

enter image description here

Where can be the issue? What am I doing wrong?

Upvotes: 0

Views: 83

Answers (2)

Barmar
Barmar

Reputation: 780974

You want:

SELECT COUNT(DISTINCT walls.wall_id)

When you write:

SELECT DISTINCT <expr>, <expr>, <expr>, ...

it effectively does the query as if there were no DISTINCT modifier, then removes all the duplicates from the result set. So when you wrote:

SELECT DISTINCT COUNT(walls.wall_id)

it simply did a normal count of all wall_id rows that met the criteria in the rest of the query.. This just returns a result, which is the total row count, and then DISTINCT removes any duplicates, which is trivial since it's just one result.

When you put the DISTINCT modifier inside COUNT(), it tells that function that it should remove duplicates while it's counting.

Upvotes: 2

semirturgay
semirturgay

Reputation: 4201

try this:

SELECT  COUNT(DISTINCT walls.wall_id) FROM walls 
LEFT JOIN wall_categories ON walls.wall_id = wall_categories.wall_id 
LEFT JOIN wall_devices ON walls.wall_id = wall_devices.wall_id
WHERE wall_categories.category_id IN (1,2,3)
AND wall_devices.device_id IN (1,2,3)
AND walls.wall_id>113;

Upvotes: 1

Related Questions