Grouping by $10 Dollar Increments in SQL

9 Sep 2005 8:12 PM

Tom Wingert

Been trying to find a way to do group orders by their dollar value in 10
dollar increments. I can get it to work by hard coding each group and union
each group, but it is slow. Wondering if anyone knows how to do this with
just one group by

This is what I got so far:

Select Sum(SubTotal), Count(OrderNumber)
From Sales
Group By OrderNumber
Having Sum(SubTotal) <= 10

Union

Select Sum(SubTotal), Count(OrderNumber)
From Sales
Group By OrderNumber
Having 10 < Sum(SubTotal) and Sum(SubTotal) <= 20

Union

Select Sum(SubTotal), Count(OrderNumber)
From Sales
Group By OrderNumber
Having 20 < Sum(SubTotal) and Sum(SubTotal) <= 30

......

Union

Select Sum(SubTotal), Count(OrderNumber)
From Sales
Group By OrderNumber
Having 500 < Sum(SubTotal)

Thanks In Advance,
Tom Wingert

9 Sep 2005 8:27 PM

Hugo Kornelis

On Fri, 9 Sep 2005 13:12:02 -0700, Tom Wingert wrote:

>Been trying to find a way to do group orders by their dollar value in 10
>dollar increments. I can get it to work by hard coding each group and union
>each group, but it is slow. Wondering if anyone knows how to do this with
>just one group by

Hi Tom,

Call me paranoid, but this smells like a homework assignment...

Hint 1: You can use expressions in the GROUP BY clause.

Hint 2: In integer division, the remainder is lost.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

9 Sep 2005 8:40 PM

Tom Wingert

LOL. Yeah, it's "homework", but I get paid for my homework :).

Not sure what you are trying to get at. Can you give me an example?

Thanks in Advance,
Tom Wingert, Software Engineer
Motorcycle-Superstore.com

Show quote

"Hugo Kornelis" wrote:

> On Fri, 9 Sep 2005 13:12:02 -0700, Tom Wingert wrote:
>
> >Been trying to find a way to do group orders by their dollar value in 10
> >dollar increments. I can get it to work by hard coding each group and union
> >each group, but it is slow. Wondering if anyone knows how to do this with
> >just one group by
>
> Hi Tom,
>
> Call me paranoid, but this smells like a homework assignment...
>
> Hint 1: You can use expressions in the GROUP BY clause.
>
> Hint 2: In integer division, the remainder is lost.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

9 Sep 2005 9:44 PM

Hugo Kornelis

On Fri, 9 Sep 2005 13:40:01 -0700, Tom Wingert wrote:

>LOL. Yeah, it's "homework", but I get paid for my homework :).

Hi Tom,

Oh. Sorry for being paranoid then.

>Not sure what you are trying to get at. Can you give me an example?

I could, but Trey Walpole beat me to it. Check his post in this thread
to see what I had in mind.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

9 Sep 2005 8:30 PM

--CELKO--

1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

2) Quit thinking like a procedural programmer and think is sets and
tables. Inparticular, auxiliaryt tables like this:

CREATE TABLE ReportRangeAmts
(range_name CHAR(15) NOT NULL,
start_amt DECIMAL (8,2) NOT NULL,
end_amt DECIMAL (8,2) NOT NULL,
CHECK (start_amt < end_amt)),
PRIMARY KEY(start_amt, end_amt)),

INSERT INTO ReportRangeAmts VALUES ('< $10', 0.00, 9.99);
INSERT INTO ReportRangeAmts VALUES ('$10 - $19.99', 10.00, 19.99);
etc.

Since you did not bother to post any DDL to help us, I will make a
guess that you do not have a subtotal column, since that would be
computed, and thus not an attribute of a base table. But you should
have the total amount for each sale.

SELECT R.range_name, SUM(sales_amt), COUNT(order_nbr)
FROM Sales AS S, ReportRangeAmts AS R
WHERE S.sales_amt BETWEEN R.start_amt AND R.end_amt
GROUP BY range_name;

9 Sep 2005 8:47 PM

Alexander Kuznetsov

one more comment:
always use union all unless you really really need union

9 Sep 2005 8:56 PM

Tom Wingert

OK. My Bad. Newbie mistake. Should have posted the rest of the info.

Thanks for the info,
Tom Wingert

PS:

Like your book. Been helpful plenty of times.

Show quote

"--CELKO--" wrote:

> 1) Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>
> 2) Quit thinking like a procedural programmer and think is sets and
> tables. Inparticular, auxiliaryt tables like this:
>
> CREATE TABLE ReportRangeAmts
> (range_name CHAR(15) NOT NULL,
> start_amt DECIMAL (8,2) NOT NULL,
> end_amt DECIMAL (8,2) NOT NULL,
> CHECK (start_amt < end_amt)),
> PRIMARY KEY(start_amt, end_amt)),
>
> INSERT INTO ReportRangeAmts VALUES ('< $10', 0.00, 9.99);
> INSERT INTO ReportRangeAmts VALUES ('$10 - $19.99', 10.00, 19.99);
> etc.
>
> Since you did not bother to post any DDL to help us, I will make a
> guess that you do not have a subtotal column, since that would be
> computed, and thus not an attribute of a base table. But you should
> have the total amount for each sale.
>
> SELECT R.range_name, SUM(sales_amt), COUNT(order_nbr)
> FROM Sales AS S, ReportRangeAmts AS R
> WHERE S.sales_amt BETWEEN R.start_amt AND R.end_amt
> GROUP BY range_name;
>
>

10 Sep 2005 2:45 AM

--CELKO--

Thank you, but which book? I have several and there is now a third
editionof SQL FOR SMARTIES. Oh, my mortgage also says "thank you" ...

9 Sep 2005 8:56 PM

Trey Walpole

one way...

select
convert(int,total)/10 as TenDollarRange,
Count(*) as OrdersInThisRange
from (
   select OrderNumber, Sum(SubTotal) as Total
    from @Sales
    Group By OrderNumber
) OrderGroup
group by convert(int,total)/10

Tom Wingert wrote:
Show quote

> Been trying to find a way to do group orders by their dollar value in 10
> dollar increments. I can get it to work by hard coding each group and union
> each group, but it is slow. Wondering if anyone knows how to do this with
> just one group by
>
> This is what I got so far:
>
> Select Sum(SubTotal), Count(OrderNumber)
> From Sales
> Group By OrderNumber
> Having Sum(SubTotal) <= 10
>
> Union
>
> Select Sum(SubTotal), Count(OrderNumber)
> From Sales
> Group By OrderNumber
> Having 10 < Sum(SubTotal) and Sum(SubTotal) <= 20
>
> Union
>
> Select Sum(SubTotal), Count(OrderNumber)
> From Sales
> Group By OrderNumber
> Having 20 < Sum(SubTotal) and Sum(SubTotal) <= 30
>
> .....
>
> Union
>
> Select Sum(SubTotal), Count(OrderNumber)
> From Sales
> Group By OrderNumber
> Having 500 < Sum(SubTotal)
>
> Thanks In Advance,
> Tom Wingert
>