Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to return all detail records when using group by

Status
Not open for further replies.

lmid

Technical User
Aug 1, 2005
8
US
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.
 
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.
 
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.
Code:
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
 
Sorry - same results as above with
Code:
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
 
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.
 
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:
Code:
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
 
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.
 
Thank you, I think this will work.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top