how to return all detail records when using group by

[lmid]

Hi,
I have a table Trans

Amount Code Date ID Trans_ID
$5 JL 5/13/06 1 5876
$5 JL 5/13/06 1 245
$6 DR 01/06/05 3 6535

The trans_ID is a unique number for each record.

I would like to do the something like:
select code, amount, date from trans
where id<10 and code<>"CD"
group by code, amount, date
having sum([amount])>2

This returns only one of the $5 records. I would like to run a query so that BOTH of the $5 records are returned.

Can anyone help me with this? Thanks in advance.

 

[DotNetGnat]

group by Trans_ID,code, amount, date

-DNG

 

[lmid]

Thank you, but that won't work. If Trans_ID is added to group by, the sum will total by each trans_ID also. If I change the criteria to be HAVING sum([amount])=10, ID #1 won't show up.

 

[traingamer]

What are you really asking for?

Do you want to display the individual rows that have a (combined code amount) over $5 for a particlar day? if so, you can't use 'Group By' which aggregates data. You could use count to get the number of records in the aggregate.

SELECT Count(Trans.Trans_ID) AS CountOfTrans_ID, Trans.Code, Sum(Trans.Amt) AS SumOfAmt, Trans.TrDate
FROM Trans
GROUP BY Trans.Code, Trans.TrDate;

returns:
Count Code SumOfAmt TrDate
1 DR $6.00 07/25/2006
2 JL $10.00 07/25/2006

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Sorry - same results as above with

SELECT Count(Trans.Trans_ID) AS CountOfTrans_ID, Trans.Code, Sum(Trans.Amt) AS SumOfAmt, Trans.TrDate
FROM Trans
GROUP BY Trans.Code, Trans.TrDate
HAVING (((Sum(Trans.Amt))>5));

having clause.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lmid]

Yes, I would like to show that individual rows that have a combined amount of over $x.

I see that I can't use Group By but I don't know how to do it. I was trying multiple queries but I haven't been able to come up with anything that worked.

 

[traingamer]

You could however, have a main query and a sub query.

The subquery is the query from the above response (named subqrTrans), and the main query as follows:

SELECT Trans.ID, Trans.Amt, Trans.TrDate, Trans.Code
FROM Trans INNER JOIN subqryTrans ON (Trans.TrDate = subqryTrans.TrDate) AND (Trans.Code = subqryTrans.Code)
ORDER BY Trans.Code, Trans.TrDate;

which gives the following results:
ID Amt TrDate Code
3 $6.00 01/06/2005 DR
1 $5.00 05/13/2006 JL
1 $5.00 05/13/2006 JL

I changed the dates to match yours.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lmid]

Thank you. I'm sorry I haven't responded, I cought a bug so I didn't get a chance to try your query yet.

 

[lmid]

Thank you, I think this will work.