CODEWITHSUNDEEP
mysqlsql
Michel Kok
Michel Kok

Reputation: 75

Invalid use of group function. But not using group

I am trying to join a table into forcing only to show the results of voucherroll that have more than 0 not printed vouchers.

But now I am getting the message that i am using a group function wrong. However, I think I'm not using group by at all. Any ideas?

SELECT * 
FROM voucherroll INNER JOIN voucher ON voucherroll.id = voucher.id_voucherroll
WHERE voucher.printed = 0
AND Count(voucher.id > 0)

An updated explanation: There are multiple rolls, with multiple vouchers. if vouchers are printed, they are not supposed to be shown. I want every roll, where the not printed vouchers are more than 0

Upvotes: 0

Views: 284

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You are using count() and it doesn't belong in where.

Perhaps this is your intention?

SELECT *
FROM voucherroll INNER JOIN
     voucher
     ON voucherroll.id = voucher.id_voucherroll
WHERE voucher.printed = 0 AND voucher.id IS NOT NULL;

It would be unusual, though, for something called id to take on NULL values.

EDIT:

I think this is what you want:

SELECT vr.*
FROM voucherroll vr
WHERE EXISTS (SELECT 1
              FROM voucher v
              WHERE vr.id = v.id_voucherroll and
                    v.printed = 0
             )

If you want more than 1, then use the less efficient:

SELECT vr.*
FROM voucherroll vr
WHERE (SELECT COUNT(*)
       FROM voucher v
       WHERE vr.id = v.id_voucherroll and
             v.printed = 0
      ) > 1;

Upvotes: 1

Robert
Robert

Reputation: 25763

Try to use subquery to count as below:

SELECT * 
FROM voucherroll
INNER JOIN voucher ON voucherroll.id = voucher.id_voucherroll
WHERE voucher.printed = 0
AND (select count(v.id)
     from voucher v
     where voucherroll.id = v.id_voucherroll ) > 0

You can't use count in where clause. It is possible in having clause, but you need to use group by.

Upvotes: 1

Related Questions