Sort breaks

Question

Sort breaks

Kerstin Safari

Right there with Babe

Points: 754
More actions
April 6, 2007 at 1:25 pm

#153513

Hi,
I have a problem that is probably very easy to figure out, but I have ALWAYS had difficulty getting my head around this.
I have an orderheader table, and in that orderheader table I have the total dollars that the customer spent on that order. I also have a sourcecode. What I need to do, based on a specific sourcecode, is figure out how many orders were placed at various total dollar amounts (0 -$10, $10.01 - $15.00, etc...). The final output should be in one table, that looks something like this:
OrdCnt Dollars
? $0-$10
$10-$15
$15-20
$20-$25
$25-$30
>$30
I'm pretty sure I'm just being very dense, but any help would be appreciated.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Try this approach

SELECT Txt, Cnt FROM ( SELECT c.Txt, COUNT(*) AS Cnt, c.Sort FROM ( SELECT '$0-$10' AS Txt, -1 AS LowLimit, 10 AS HighLimit, 0 AS Sort UNION ALL SELECT '$10-$15', 10, 15, 1 UNION ALL SELECT '$15-20', 15, 20, 2 UNION ALL SELECT '$20-$25', 20, 25, 3 UNION ALL SELECT '$25-$30', 25, 30, 4 UNION ALL SELECT '>$30', 30, 2147483647, 5 ) AS c LEFT JOIN ( SELECT Number FROM master..spt_values WHERE Name IS NULL AND Number BETWEEN 0 AND 255 ) AS o ON o.Number > c.LowLimit AND o.Number <= c.HighLimit GROUP BY c.Txt, c.Sort ) AS y ORDER BY Sort

N 56°04'39.16"
E 12°55'05.25"

Kerstin Safari Right there with Babe Points: 754 More actions · Answer 2

Maybe I'm NOT being dense. This is pretty hard to follow. I'll try to figure it out.

Thanks

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

The derived table y you must replace with the aggregation for you OrderHead table.

N 56°04'39.16"
E 12°55'05.25"

Kerstin Safari Right there with Babe Points: 754 More actions · Answer 4

So if I do a pass before all of this, into #temp1, for instance, that just has the values for the product dollars in it, how would that relate to the derived table 'y'? Or would I need to have more than just the dollars in it?

thanks

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

What about ...

SELECT

'$0-$10' = Sum(CASE WHEN orderAmount BETWEEN 0 AND 9.99 THEN 1 ELSE 0 END)

,'$10-$15' = Sum(CASE WHEN orderAmount BETWEEN 10 AND 14.99 THEN 1 ELSE 0 END)

,'$15-$20' = Sum(CASE WHEN orderAmount BETWEEN 15 AND 19.99 THEN 1 ELSE 0 END)

,'$20-$25' = Sum(CASE WHEN orderAmount BETWEEN 20 AND 24.99 THEN 1 ELSE 0 END)

,'$25-$30' = Sum(CASE WHEN orderAmount BETWEEN 25 AND 29.99 THEN 1 ELSE 0 END)

,'>$30' = Sum(CASE WHEN orderAmount >= 30 THEN 1 ELSE 0 END)

FROM

yourTable

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Kerstin Safari Right there with Babe Points: 754 More actions · Answer 6

I'll try this too, to see if will be any easier to figure out, although I DID just figure out the other method (but I think my brain hurts, now, and I have to pass this on to someone who knows even less than I do about this stuff) It looks like a viable method, too. (that's what's so fun about SQL, there are usually several ways to accomplish the same thing)

thanks

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 7

Peter,

I'm curious to your thoughts on my solution. My personal opinion is that it's easier to read/understand.

Jason

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Kerstin Safari Right there with Babe Points: 754 More actions · Answer 8

The difference that I find is formatting. Peter's does the up and down output, whereas yours (Jason) is right to left (or left to right, depending). It's all on one line, regardless. I have submitted both to the person who will be using them, to see how they want to proceed.

Thanks to both of you for replying.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

Jason, either is equally good for now.

But... If original poster (OP) wants to extend the query to include sum of total order amount for each line, my query is easier to extend.

For example, as for now, OP wants only the count of orders between the prewritten limits. Ok, fine.

Bu tomorrow, she also wants to sum up all '$5-$10' orders to include a total.

With my query, just add a SUM(o.OrderAmount) in the SELECT case.

With your query, we have to add another full CASE clause.

N 56°04'39.16"
E 12°55'05.25"

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 10

Peter,

I did notice that but was curious.

Thanks, and we're always learning.

Jason

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg