Newbie SQL Server aggregate query

Question

I have two order files, Header records (MRRECVH) and detail records (MRRECVD).

In order to see an order total amount, I need to summarize all the line items in the MRRECVD file where the Control# (common to both files and unique to the order). I only want to see MRECVH records with a total order amount > 0.

Here is what I have (that isn't working):

    USE ZZZ

    SELECT a.NAME, a.RECV_DATE,
      ( SELECT SUM(b.RECV_AMOUNT)
        FROM MRRECVD b
        WHERE ( a.CONTROL = b.CONTROL )
      ) AS 'TOTAMT'
    FROM MRRECVH a
    GROUP BY a.CONTROL
           , a.BANK
           , a.NAME
           , a.CHECK_NUM
           , a.RECV_DATE ;

This gives me everything, but I only want records with a total detail amount (sum of b.RECV_AMOUNT) > 0

When I try to aggregate SUM(b.RECV_AMOUNT) in my WHERE clause I get errors. I've tried many other variations as well. What am I missing?

Answer 1

just use an HAVING clause, when you want to use conditions on an aggregated field

By the way you can do your query like that.

select a.Name, a.recv_Date, sum(b.recv_amount) as totalamt
from MRRECVH a
inner join mrrecvd b on a.control  = b.control
group by a.control, a.bank, a.name, a.check_num, a.recv_date
having sum(b.recv_amount) > 0