Prev: Nested Set Model - finding the level 1 parent node of a child node
Next: bit datatype displays as 'true'
From: Plamen Ratchev on 26 Jul 2010 22:02
Can you explain how this works and provides the correct answers?
Tables have no order and nothing guarantees that the first CASE
expression will sum 7, and the next one 7 + 3 = 10... Maybe I
misunderstood your initial request and order of summing really does
not matter, but then not sure what is the purpose of the query.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 27 Jul 2010 04:09
Wes Groleau (Groleau+news(a)FreeShell.org) writes:
> On 07-26-2010 16:13, Erland Sommarskog wrote:
>> It certainly had help if you had posted a CREATE TABLE statement for your
>> table and some sample data. But if I am to guess, this works:
>
> I assumed that reducing the question to the essentials would
> make discerning the answer easier.
>
>> SELECT event, A, B, C,
>> [P< 1] = SUM(CASE WHEN P< 1 THEN 1 ELSE 0 END),
>> [P< 2] = SUM(CASE WHEN P< 2 THEN 1 ELSE 0 END),
>> [P< 3] = SUM(CASE WHEN P< 3 THEN 1 ELSE 0 END)
>> FROM tbl
>> GROUP BY event, A, B, C
>>
>> If this is flat wrong, please read my response from the beginning and act
>> accordingly.
>
> Actually, it's quite close. Perhaps my failure to post a zillion
> words was not a failure after all. The only flaw is that "event"
> was a generic description of each record rather than a field in
> the record.

In all honesty, what I provided was a guess. Plamen and Joe took your
question to be about running totals, and I can hardly blame them.

I do spend quite some time of answer people's questions on the newsgroups,
and believe of that times to understand what the poster is asking for.
You don't have to provide a zillion words, but a standard recommendation
is that you provide:

o CREATE TABLE statement for your table(s), preferrably simplified.
o INSERT statement with sample data.
o The desired result given the sample.
o A short description of the underlying business rules.
o Which version of SQL Server you are using.

This helps to make clarify things, and also makes it possible to
copy and paste into SSMS to develop a tested solution.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Wes Groleau on 27 Jul 2010 09:43
On 07-27-2010 04:09, Erland Sommarskog wrote:
> You don't have to provide a zillion words, but a standard recommendation
> is that you provide:
>
> o CREATE TABLE statement for your table(s), preferrably simplified.
> o INSERT statement with sample data.
> o The desired result given the sample.
> o A short description of the underlying business rules.
> o Which version of SQL Server you are using.

I can see that all of this would be clearer than what I posted,
but I thought I got the basic idea in one screen where all of this
would have taken a lot of scrolling and examining to integrate
mentally. I guess people just approach problems differently.

The most embarrassing part is that I have done plenty with
aggregates and CASE and should have thought of that solution.
And having worked with three versions of SQL Server, I should
have known to post the version.

Anyway, thanks for the answer and no hard feelings to those who tried.

--
Wes Groleau

The Miracle Worker?
http://Ideas.Lang-Learn.us/WWW?itemid=668
From: Erland Sommarskog on 27 Jul 2010 17:46
Wes Groleau (Groleau+news(a)FreeShell.org) writes:
> I can see that all of this would be clearer than what I posted,
> but I thought I got the basic idea in one screen where all of this
> would have taken a lot of scrolling and examining to integrate
> mentally. I guess people just approach problems differently.

If you want to read a story about Sherlock Holmes, would you have a
complete book, or a very compact summary of the plot, where involved
people are introduced without a context?

The problem is that when you make your compact presentation, you know
exactly what you are talking about. But we on the other end do not.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Wes Groleau on 27 Jul 2010 19:55
On 07-27-2010 17:46, Erland Sommarskog wrote:
> The problem is that when you make your compact presentation, you know
> exactly what you are talking about. But we on the other end do not.

Fair enough. Then again, I've seen people presented with the full
details get side-tracked by details that are irrelevant to the
question. For example, in my application,

A char(1),
B char(1),
C char(4)

I said merely that there were columns A, B, and C because the solution
would have been exactly the same if they were varbinary(2000), money,
or dates.

If I get stuck again, I will certainly consider the preferred
questioning style should I choose to post here.

Or maybe I'll go back to another forum where I got the same answer,
but where they seem to prefer the style I prefer. :-)

--
Wes Groleau

Do people learn languages by studying grammar? Of course.
http://Ideas.Lang-Learn.us/barrett?itemid=996
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Nested Set Model - finding the level 1 parent node of a child node
Next: bit datatype displays as 'true'