CODEWITHSUNDEEP
mysqlcount
silkfire
silkfire

Reputation: 25935

COUNT(*) returning multiple rows instead of just one

Why does COUNT() return multiple rows when I just need the total count of how many rows my query generates?

Should return 1078.

enter image description here

Upvotes: 12

Views: 16051

Answers (4)

Sushil Deshmukh
Sushil Deshmukh

Reputation: 114

Write the above query as subquery then it will give proper result and dont use group by

select count(*) from (select articles.id from 'contract_prices' left join 'articles' on
    (arcticles.id = contract_prices.article) 
    where 'contract_to' >= curdate()
    )

Upvotes: 0

Kangkan
Kangkan

Reputation: 15571

The COUNT() is working as expected. When you put a group by clause, the count() gives you the result for GROUP BY. If you wish to get the count of rows in a query that includes group by, use it as a subquery instead.

Something like:

SELECT COUNT(*) FROM (SELECT * FROM `table`
                      GROUP BY `column1`) AS `a`

Upvotes: 33

MarshalSHI
MarshalSHI

Reputation: 625

Do not use group by, it will count the number of each group.

Upvotes: 1

Tomalak
Tomalak

Reputation: 338158

Well, simple answer. Don't GROUP BY if you don't need groups.

Either use COUNT(DISTINCT articles.company) without GROUP BY or keep the the GROUP BY and wrap the whole query in a SELECT COUNT(*) FROM (...) AS data, if you want to count the groups.

Upvotes: 3

Related Questions