Re: [GENERAL] Question About Aggregate Functions

On 9/13/06, Brandon Aiken <BAiken(at)winemantech(dot)com> wrote:
>
> I think I mistakenly sent this to General instead of Novice. Oops.
>

Oh well. I posted back to Novice.

Yeah, I either skipped over or forgot the bit in the OP about bools. Mea
> culpa.
>

Hehe.

You should be able to use OR instead of AND in any logical expression.
>
>
>
> Well this sounds more like what I want. Given t2.fielda, t2.fieldb,
> t2.fieldc, any one (or all three) could be true, but frequently at least
> one of the fields is false. Initially, all of the fields might be unknown
> (thus NULL) for a given item until I am able to investigate the items to
> determine TRUE/FALSE. I frequently have items that are inactive, and thus
> unable to determine any of attributes in t2.
>
> My end result needs to be a count of all the values in each field where
> the value is TRUE, as opposed to FALSE or NULL.
>
>
>
> Yeah, I would probably run 4 separate, simple queries. That will get you
> the best performance since you're doing no JOINs and no composite queries.
>

There are actually 12 fields involved. I figured if I could just learn how
to handle the first few, I could probably take it from there.

If you need to enter the results into another table, try INSERT … to insert
> the defaults and any primary key you have (like timestamp), then four UPDATE
> … SELECT statements.
>

Cool. I'll play around with it a while, see what I come up with.

The real problem with NULLs is some of the (in my mind) nonsensical results
> you get, especially with logical operators:
>
> NULL AND TRUE => NULL
>
> NULL OR TRUE => TRUE
>
> NULL AND FALSE => FALSE
>
> NULL OR FALSE => NULL
>
>
>
> Plus you have to use IS instead of = since any NULL in an = expression
> makes the result NULL (yes, this is an error in my previous queries). NULL
> just has all these special cases. I find it much nicer to avoid it wherever
> possible since it has somewhat unpredictable results.
>

Wow. Guess that could give me some interesting results, depending on what I
do! Thanks again for the input.

Don