Hi all
I need to perform a summation on a column of a table based on a
criteria given in another column in the same table. The catch is i
need to perform different sums according to the number of criterias in
the criteria column, in a single query. the structure of the table is
somethinmg like this (only relevant columns are shown)

TABLE1
Value - numeric(20,6)
Month - int
indicator - bit

Now i need to do something like:

SELECT Month, SUM(Value) FROM TABLE1
WHERE indicator = 1
GROUP BY Month

and also

SELECT Month, SUM(Value) FROM TABLE1
WHERE indicator = 0
GROUP BY Month


How can i do this in a single query, something like this:
SELECT Month, SUM(Value where indicator=1), SUM(Value where
indicator=0) and so on .......

Could any body please me on this ?

You need to stop using reserved words (MONTH, INDICATOR, etc) for
column names:

SELECT sales_month,
SUM(CASE WHEN flag = 0 THEN vague_value ELSE NULL END) AS
foo_tot,
SUM(CASE WHEN flag = 1 THEN vague_value ELSE NULL END) AS
bar_tot,
FROM Foobar;

It is also a bad idea to use proprietary BIT data types to fake
assembly language style programming. SQL is a predicate language;
that is, we discover a fact with a predicate rather than set a flag.

Try:

SELECT
Month
, SUM(CASE WHEN indicator = 0 THEN Value ELSE 0 END) Value0
, SUM(CASE WHEN indicator = 1 THEN Value ELSE 0 END) Value1
FROM TABLE1
GROUP BY Month


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Darsin" <darsin@gmail.com> wrote in message
news:8fc9e52e-1606-44d9-a722-4ae0f29542b6@s13g2000prd.googlegroups.com...
Hi all
I need to perform a summation on a column of a table based on a
criteria given in another column in the same table. The catch is i
need to perform different sums according to the number of criterias in
the criteria column, in a single query. the structure of the table is
somethinmg like this (only relevant columns are shown)

TABLE1
Value - numeric(20,6)
Month - int
indicator - bit

Now i need to do something like:

SELECT Month, SUM(Value) FROM TABLE1
WHERE indicator = 1
GROUP BY Month

and also

SELECT Month, SUM(Value) FROM TABLE1
WHERE indicator = 0
GROUP BY Month


How can i do this in a single query, something like this:
SELECT Month, SUM(Value where indicator=1), SUM(Value where
indicator=0) and so on .......

Could any body please me on this ?

Hi
SUM(CASE WHEN indicator =0 THEN value END ) as val1
SUM(CASE WHEN indicator =1 THEN value END ) as val1
FROM tbl




"Darsin" <darsin@gmail.com> wrote in message
news:8fc9e52e-1606-44d9-a722-4ae0f29542b6@s13g2000prd.googlegroups.com...
> Hi all
> I need to perform a summation on a column of a table based on a
> criteria given in another column in the same table. The catch is i
> need to perform different sums according to the number of criterias in
> the criteria column, in a single query. the structure of the table is
> somethinmg like this (only relevant columns are shown)
>
> TABLE1
> Value - numeric(20,6)
> Month - int
> indicator - bit
>
> Now i need to do something like:
>
> SELECT Month, SUM(Value) FROM TABLE1
> WHERE indicator = 1
> GROUP BY Month
>
> and also
>
> SELECT Month, SUM(Value) FROM TABLE1
> WHERE indicator = 0
> GROUP BY Month
>
>
> How can i do this in a single query, something like this:
> SELECT Month, SUM(Value where indicator=1), SUM(Value where
> indicator=0) and so on .......
>
> Could any body please me on this ?

> It is also a bad idea to use proprietary BIT data types to fake
> assembly language style programming. SQL is a predicate language;
> that is, we discover a fact with a predicate rather than set a flag.

What if the fact IS a flag, and is not based on other data in your database?
e.g. did the patient visit Congo anytime between 1978 and 1989? Instead of
a "flag" saying yes or no, would you rather store each patient's entire
travel itineraries for the period in question? What do you when that is not
available, but the patient does know that he/she was there during that time
period?

You can't answer 8 billion different design questions with your four
standards-based rules. Real world design and development dictates a little
more flexibility than that.

>> What if the fact IS a flag, and is not based on other data in your database? <<

It might or might not be based on other data; that is the problem. I
don't want to write triggers to constantly update the flags.

>> e.g. did the patient visit Congo anytime between 1978 and 1989? Instead of a "flag" saying yes or no, would you rather store each patient's entire travel itineraries for the period in question? <<

The alternative is a set of flags that cover all of the countries on
Earth, and all of the possible time periods Oh, I also hope that
you created exactly the right flags, since they carry so little
data.

Eventually, I will probably have to have his travel itineraries to
follow a disease pattern. I have a friend who is an epidemiologist at
the CDC and that is how they do it.

>> What do you when that is not available, but the patient does know that he/she was there during that time period? <<

You put it the chart as free-form data and then you check his passport
records. Hey, this is the usual missing/fuzzy data problem. What did
you do when you never had a flag for that "bit" of data? What do you
do when the important year was 1990? More bit flags? Have you ever
considered what the triggers and constraints to keep the flags
current?

> You put it the chart as free-form data and then you check his passport
> records. Hey, this is the usual missing/fuzzy data problem. What did
> you do when you never had a flag for that "bit" of data? What do you
> do when the important year was 1990? More bit flags? Have you ever
> considered what the triggers and constraints to keep the flags
> current?

The fact remains, some things are just that, flags... no further information
necessary, no updating required.

On Mon, 24 Mar 2008 10:04:23 -0700 (PDT), --CELKO-- wrote:

>Eventually, I will probably have to have his travel itineraries to
>follow a disease pattern. I have a friend who is an epidemiologist at
>the CDC and that is how they do it.

Hi Joe,

But not every organization that collects information is interested in
tracking disease patterns.

I donate blood on a regular basis. And every time I go, I have to fill
out some forms. One of the things they want to know is whether I have
visited the United States during the last 90 days - because if I have, I
can't donate blood at that time and they'll ask me to make a new
appointment. I think it has to do with the possibility of infection with
the West Nile virus.

Anyway, they don't ask me for my travel itinerary or whatever, just one
thing: "have you been in the United Stated during the last 90 days"? and
two check boxes conveniently marked yes and no. How would you model
that?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis