SQL command to GROUP BY buckets...

[Lyndon]

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.

 

[Dirk Goldgar]

In

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..?

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"?

 

[Dirk Goldgar]

In Lyndon <[email snipped]> wrote:

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.

 

[Lyndon]

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.

 

[Dirk Goldgar]

In

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.

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.