CODEWITHSUNDEEP
mysql
clarkk
clarkk

Reputation: 27685

using count and suppress/ignore group by

Is it possible to have count in the select clause with a group by which is suppressed in the count? I need the count to ignore the group by clause

I got this query which is counting the total entries. The query is generic generated and therefore I can't make any comprehensive changes like subqueries etc.

In some specific cases a group by is needed to retrieve the correct rows and because of this the group by can't be removed

SELECT count(dv.id) num
FROM `data_voucher` dv
LEFT JOIN `data_voucher_enclosure` de ON de.data_voucher_id=dv.id
WHERE IF(de.id IS NULL,0,1)=0
GROUP BY dv.id

Upvotes: 3

Views: 4315

Answers (2)

clarkk
clarkk

Reputation: 27685

Have found an alternative where COUNT DISTINCT is used

SELECT count(distinct dv.id) num
FROM `data_voucher` dv
LEFT JOIN `data_voucher_enclosure` de ON de.data_voucher_id=dv.id
WHERE IF(de.id IS NULL,0,1)=0

Upvotes: 0

zmo
zmo

Reputation: 24812

Is it possible to have count in the select clause with a group by which is suppressed in the count? I need the count to ignore the group by clause

well, the answer to your question is simply you can't have an aggregate that works on all the results, while having a group by statement. That's the whole purpose of the group by to create groups that change the behaviour of aggregates:

The GROUP BY clause causes aggregations to occur in groups (naturally) for the columns you name.

cf this blog post which is only the first result I found on google on this topic.

You'd need to redesign your query, the easiest way being to create a subquery, or a hell of a jointure. But without the schema and a little context on what you want this query to do, I can't give you an alternative that works.

I just can tell you that you're trying to use a hammer to tighten a screw...

Upvotes: 4

Related Questions