Doing Summation on multiple criterias on the same column in a singlequery

[Alex Kuznetsov]

Re: Doing Summation on multiple criterias on the same column in asingle query

On Mar 28, 4:32 pm, --CELKO-- <jcelko...@eart hlink.netwrote:

So, your yes/no answers and your actual underlying facts are just two different kinds of facts. <<

>
Yes! That is one of my points. Flags are at a different and
derivable level of aggregation.

Of course not - human answers may and do disagree with the underlying
facts. The fact that you put a check on signed and dated sheet of
paper cannot be derived from your actual smoking history.

[Tony Rogerson]

Re: Doing Summation on multiple criterias on the same column in a single query

Aaron,

Take a look at Hugo's post - celko is completely ignoring because he can't
answer it.

Here's an example of a blood donor form -
http://www.bloodbook.com/form-donorpre.html - we'd all love to see how celko
would model it.

--
Tony Rogerson, SQL Server MVP

http://sqlblogcasts.com/blogs/tonyrogerson

[Ramblings from the field from a SQL consultant]

http://sqlserverfaq.com

[UK SQL User Community]


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in message
news:A0FDAEBF-E1A5-40AB-AA30-C3D3A06416A1@mi crosoft.com...

>They might only keep data on Lemonade drinkers, such as liters per
>month drunk. The flag would be redundant.

>
Quit changing my question!
>

>>>No, but if I was a company that could only sell to people in Texas, I
>>>might ask "Do you live in Texas?" exactly once. If you live in some
>>>other state, survey over. <<

>>
>That is done at input time and no data is being kept in the RDBMS. As
>you said, "survey over" and we don't bother with it.

>
Not necessarily true! What if the next question is, "Would you ever
consider moving to Texas?"
>
We can go on and on all day, and I can come up with plenty of questions
that can and should be modeled as T/F, Y/N, 1,0. And you can argue every
single one of them with whatever arguments you want to make up at runtime.
You will still be wrong.
>

>So how do I set this flag? I will be monitoring my network; each
>server will have a (login_time, logout_time) pair to show when it is
>on-line;

>
WRONG! I did not say online/offline; it does not have anything to do with
whether the server is "up" or not. That is something that can be detected
at runtime, anyway. Ping server. Response? Up! No response? Down!
Why should I bother storing this, unless I am interested in uptime
history? (I already explained that I am not.) Maybe I am moving the
server in or out of a cluster, or am doing maintenance on it, or maybe I
am isolating it to test the next version of my application. Stop
pigeon-holing scenarios into your pigeon-holed view of how the real world
works!
>
A

[Geoff Schaller]

Re: Doing Summation on multiple criterias on the same column in a single query

Sorry to correct you but you forgot one line of code:

SET ANSI_INFLEXIBIL ITY ON


Joe wouldn't take it any other way <g>



"Tony Rogerson" <tonyrogerson@t orver.netwrote in message
news:781DAEF8-8C8E-4805-A595-7868FF9078DC@mi crosoft.com:

We're not even talking about the same thing here. You are blindly
grasping for an example of something that IS computed and clearly IS NOT
what anyone in their right mind would consider a "flag"... play fair and
honest, celko!

>
create table celko_answer_cl utching (
forum_nntp_guid uniqueidentifie r not null primary key,
>
post_author varchar(200) not null,
>
is_straw char(1) not null check( is_straw = 'Y' or ( post_author <>
'--celko--' and is_straw in ( 'Y', 'N' ) ) ) )
)

[--CELKO--]

Re: Doing Summation on multiple criterias on the same column in asingle query

>Unless we're actually storing a Yes/No, True/False, or some other two-valued data; as would seem to be indicated by the "INDICATOR" column name. <<

I have no trouble with a two-valued domain; I even gave an example of
the Rh factor in blood typing. You just do not see them very often in
the real world.

>INDICATOR, as an aside, is not a reserved word in SQL Server. <<

But it is for embedded SQL in the X3J languages adn SQL Server has an
embedding even if MS does not advertise it. There is more to the
world of RDBMS than just .NET programming.

[Aaron Bertrand [SQL Server MVP]]

Re: Doing Summation on multiple criterias on the same column in a single query

several tests (predicates and formulas)" as opposed "We gave John a

higher credit rating by setting a flag and have no idea why!")

And do you think the fact that John likes lemonade should be described by
other facts in the database (e.g. what town he spent middle school in)? Or
do you think maybe that is just a fact on its own, not derived from
predicates, formulas, aggregations, or relayed from other facts in any way?

[--CELKO--]

Re: Doing Summation on multiple criterias on the same column in asingle query

>And do you think the fact that John likes lemonade should be described by other facts in the database <<

How about a list of beverages, with the value Lemonade in it? That
would be nominal scale and not a flag. When we decide to research
other beverages, we extend the scale. Unlike a flag, I can ask how
much of a given beverage he drinks.

I extrapolate that if John drinks s certain number of Cokes and is of
a certain age, then I stand an 85% chance of selling him Pepsi (i.e.
kids the sweeter Pepsi to Coke). You cannot get that kind of
information from flags.

[Aaron Bertrand [SQL Server MVP]]

Re: Doing Summation on multiple criterias on the same column in a single query

How about a list of beverages, with the value Lemonade in it?

As I have asked several times already, what if they are only interested in
lemonade? Why should they bother with a list?

Anyway, you are still clearly either missing the point or intentionally
disregarding it. This is not about lemonade; this is about the fact that
some things are just yes/no indicators on their own, without "help" from
other facts. If you don't get it, you don't get it, and I'm afraid I can't
help to educate you any further. :-(

[Aaron Bertrand [SQL Server MVP]]

Re: Doing Summation on multiple criterias on the same column in a single query

>>

Anyway, you are still clearly either missing the point or intentionally
disregarding it. This is not about lemonade; this is about the fact that
some things are just yes/no indicators on their own, without "help" from
other facts. If you don't get it, you don't get it, and I'm afraid I can't
help to educate you any further. :-(

To my (mostly) unbiased eye, it looks like both sides are missing the
point (either intentionally or otherwise)... Joe has already said
that he accepts that there are rare cases where a 2 value domain is
valid, but that in *most* cases modelling a flag is not "correct".

>>

Well, to be fair, I did not say that *most* columns should be a two-value
flag, either. But in my experience they are more common than Celko's
"advice" would lead one to believe.

[jhofmeyr@googlemail.com]

Re: Doing Summation on multiple criterias on the same column in asingle query

Well, to be fair, I did not say that *most* columns should be a two-value

flag, either.  But in my experience they are more common than Celko's
"advice" would lead one to believe.

Perhaps what I should have said is "in *most* cases where a flag has
been modelled, it is not "correct" to have done so" :)

[--CELKO--]

Re: Doing Summation on multiple criterias on the same column in asingle query

>As I have asked several times already, what if they are only interested in lemonade? Why should they bother with a list? <<

If the only concern is about lemonade preferences and consumption,
wouldn't everything in that table deal with lemonade consumption? If
so, why would they bother with a flag? It would be like a Personnel
table with a flag that asks "Are you an employee?" when the answer
would have to be "yes" to get into the table.

Let me recover a bit from my physical exam, x-rays and booster shots
and see if I can get a short article about scales, measurements and
data values versus question/answer and other types of flags.

[Aaron Bertrand [SQL Server MVP]]

Re: Doing Summation on multiple criterias on the same column in a single query

If the only concern is about lemonade preferences and consumption,

wouldn't everything in that table deal with lemonade consumption?

Celko, you are too much.