CODEWITHSUNDEEP
mysql
Mohamed Mufeed
Mohamed Mufeed

Reputation: 1580

How do I filter a group by checking if a value is present in the group in mysql?

I have a journals and transactions table. The journals is the parent and the transactions its child.

What I am trying to do is group the transactions table to check the sum(amounts). But I don't need the whole transactions - only those where an account is present

I know this is an awful description, but I have no idea how to even approch this. so if you guys need more details please let me know.

basically, this is the query that I want to achieve. But its not working for some reason which i don't know.

select 
    t1.*
from 
    (
        select 
            journal_id,
            sum(amount)
            group_concat(quote(account)) as acc_codes 
        from
            `transactions` 
        group by journal_id 
    ) as t1
where '4500' in (t1.acc_codes)

Upvotes: 1

Views: 28

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

Ditch the subquery approach and instead add a HAVING clause assertion which checks for the presence of the account:

SELECT journal_id, SUM(amount) AS total
FROM `transactions` 
GROUP BY journal_id
HAVING SUM(account = '4500') > 0;

Upvotes: 1

Related Questions