groupby question

1. Senior Member
Join Date
Dec 1969
Posts
172

## groupby question

I have a tree database which looks like something like this:<BR><BR>type peracre diam<BR>----------------------------------------------------------------<BR>JP 1.25 5.5<BR>JP 1.25 6.6<BR>BF 1.23 7.2<BR>BS 1.15 1.3<BR>JP 1.25 2.5<BR>BF 2.5 8.3<BR>BF 1.30 7.5<BR>BS 1.5 4.4<BR>BS 1.25 3.3<BR>------------------------------------------------------<BR>there are two columns:type and peracre.<BR>I want to group the trees according to their type but also the total peracre value for each group should be equal to 5. The final table should look like this<BR><BR>type peracre count Avg(diam)<BR>------------------------------------------<BR>JP 5.0 4 6.2<BR>JP 5.0 3 4.5<BR>BF 5.0 2 1.6<BR><BR><BR>I tried groupby,having clauses but I could not succeed.Can someone please help me.Thank you very much,<BR>Cengo

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## I don't get it...

The total peracre for all the JP records is only 3.75<BR><BR>How/why do you want to force that up to 5.0??<BR><BR>And how do you force the count to 4 when there are only 3 JP records?<BR><BR>And where does your average diameter of 6.2 come from?<BR><BR>(5.5 + 6.2 + 2.5) / 3 is 4.73 (average of the 3 numbers) by my calculations.<BR><BR>If you can explain your very very weird math, maybe we can help with the SQL?<BR><BR>And how come you have JP listed twice????<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•