mysql group by with where returns totally other results

Question

I try to select in a one to many relation childs where records of childs is 1. For fetching childs with one record I use the following query.

Here is the simple query which works if I do not use wherestatement

select a.iid, 
account_id,
count(*) as props

from  accounts_prop a
group by a.account_id
having props = 1

when I use where I get back totally other result. In this case I get records which shows that props are having 1 record but actually having more than one

    select a.iid, 
    account_id,
    count(*) as props

    from   accounts_prop a
    where a.von >= '2017-08-25'
    group by a.account_id

having props = 1

What I'm missing in this case

Answer 1

Upon closer inspection, your original query is not even determinate:

SELECT
    a.iid,              -- OK, but which value of iid do we choose?
    a.account_id,
    COUNT(*) AS props
FROM accounts_prop a
GROUP BY a.account_id
HAVING props = 1

The query makes no sense because you are selecting the iid column while aggregating on other columns. Which value of iid should be chosen for each account? This is not clear, and your query would not even run on certain versions of MySQL or most other databases.

Instead, put your logic to find accounts with only a single record into a subquery, and then join to that subquery to get the full records for each match:

SELECT a1.*
FROM accounts_prop a1
INNER JOIN
(
    SELECT account_id
    FROM accounts_prop
    GROUP BY account_id
    HAVING COUNT(*) = 1
) a2
    ON a1.account_id = a2.account_id
WHERE a1.von >= '2017-08-25'