Group By, do I want a Coalesce?

Question

Group By, do I want a Coalesce?

jasbrand

SSC Rookie

Points: 25
More actions
August 28, 2008 at 3:06 pm

#189895

Writing a query to get a few SUMS and a Count from a single table. I am basically trying to sum several columns while watching another separate column that is NULL. If that column is not null I want to keep those sums and start over again.
Example of the table:
Bales TMY TMY2 CONS
77.468.52860.32NULL
7268.52864.63NULL
66.968.52868.15NULL
5768.52854.01NULL
60.868.52854.8 NULL
62.368.52857.43NULL
65.368.52859.68NULL
60.668.52862.65NULL
59.368.52852.128121633
63.155.02144.9 NULL
5955.02142.1 NULL
45.155.02152.33NULL
48.355.02149.44NULL
54.955.02147.89NULL
61.455.02147.38NULL
55.455.02137.275933421
62.839.61336.75NULL
33.739.61357.22NULL
33.439.61353.69NULL
36.939.61349.82NULL
37.139.61336.49NULL
24.339.61334.69NULL
20.239.61336.99NULL
23.839.61336.2111009037
Now I basically want to Sum the TMY and TMY2 and count the Bales up to the point where the Cons is not NULL. I would like 3 records from my query, something basically like this:
Count(Bales)Sum(TMY)Sum(TMY2)Cons
9 616.752533.79 8121633
7 385.147321.31 5933421
8 316.904341.86 11009037
I am trying the basic stuff and striking out. Any help?
Here's some SQL that I thought could do the trick

Viewing 5 posts - 1 through 4 (of 4 total)

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

Are there other columns in this table? Looking at your example, I'm not sure how to enforce the order of the rows so that it properly keeps the related records together.

jasbrand SSC Rookie Points: 25 More actions · Answer 2

Yes, there is a date field, primary key, etc. I should've included those in fact.

Suffice it to say there will be a uniqueidentifier for each row and a unique date for each row too.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Yes, but what defines the Order?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 4

if that is the exact values for the bales then they it looks to me you could use a union query for the 3 results (each result relates to a query) and use between to get only the bales you want for each result.