SQL command to GROUP BY buckets...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lyndon - 28 Mar 2007 17:46 GMT

Hi all,

I have a table of data and want to SUM() a column of values but based
on the values in another column:

Val1 Val2
1 2
2 3
4 5
6 7

Can I Sum Val1 based on grouping Val2 into buckets of <> 4 so the
result is:

Expr001
3
10

SELECT Sum(Val1)
FROM mytable
GROUP BY Val2
HAVING what goes here..?

Thanks,
Lyndon.

Reply to this Message

Dirk Goldgar - 28 Mar 2007 18:16 GMT

> Hi all,
>
[quoted text clipped - 18 lines]
> GROUP BY Val2
> HAVING what goes here..?

I'm not sure I understand what grouping it is you want. Do you mean
that you want to have one group that is "Val2 < 4", and another group
that is "Val2 > 4"?

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Lyndon - 29 Mar 2007 09:59 GMT

> I'm not sure I understand what grouping it is you want. Do you mean
> that you want to have onegroupthat is "Val2 < 4", and anothergroup
> that is "Val2 > 4"?

Yes that is correct. I would like to group Val2 into two buckets, one
less than 4, one greater than 4 so the SUM() function sums Val1
according to these groupings. Imagine if Val2 was actually "Country"
and there was two countries, "USA" and "UK", I could use the GROUP BY
to group these countries together and produce a sum of all the values
that fell into each country bucket. I want to do the same thing but
set a threshold on a value and group by that, so treat one set of
values as a group if Val2 is less than 4 and set the other set of
values as a group if Val2 is greater than 4, and sum accordingly.

Reply to this Message

Dirk Goldgar - 29 Mar 2007 15:01 GMT

>> I'm not sure I understand what grouping it is you want. Do you mean
>> that you want to have onegroupthat is "Val2 < 4", and anothergroup
[quoted text clipped - 9 lines]
> values as a group if Val2 is less than 4 and set the other set of
> values as a group if Val2 is greater than 4, and sum accordingly.

I think you could use SQL along these lines:

SELECT
Sum(Val1) AS GroupSum,
IIf([Val2]<4,"<4",">4") AS GroupName
FROM mytable
WHERE mytable.Val2)<>4
GROUP BY IIf([Val2]<4,"<4",">4");

Note that I've specifically excluded those records where Val2 = 4. That
seemed to be what you want, but it would be easy to put in a third group
for that condition.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Dirk Goldgar - 28 Mar 2007 18:17 GMT

P.S. I strongly suggest that you obfuscate your e-mail address when
posting to newsgroup. Spammers and viruses routinely scan the
newsgroups for e-mail addresses to use as targets, so posting your true
e-mail address can quickly get you buried in spam and malicious
messages.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message