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

[Darsin]

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 help me on this ?

[Tom Moreau]

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" <da****@gmail.comwrote in message
news:8f**********************************@s13g2000 prd.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 help me on this ?

[Uri Dimant]

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


"Darsin" <da****@gmail.comwrote in message
news:8f**********************************@s13g2000 prd.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 help me on this ?

[Hugo Kornelis]

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

[--CELKO--]

>"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? <<

They don't care about your trips to AIDS infected Africa? Avian flu
in China? Mad Cow Disease in the UK? I think I would go with three
tables -- Donors for your general demographics, Donations for a
history of donations and a Quarantine list with countries and dates
that would exclude a donation at this time.

I would first check the date of the last donation to be sure that you
are not giving too much and too often. In the US you get recognition
for donating over a gallon, etc. We want a history in the DB.

Then I would ask "what countries have you visited since << insert
date>>?" One of the options could include the Netherlands, altho the
front end might present it as "None" or something similar to the
donor. When the situation changes, I change the Quarantine list.

[Darsin]

On Mar 24, 6:32*pm, "Tom Moreau" <[e-mail]:

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 * Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau

"Darsin" <[e-mail] in message

news:8f**********************************@s13g2000 prd.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 help me on this ?

Hi all,

First of all my apologies for using Month, Value, etc keywords in the
example, will remember to not do it again, and thank you for a prompt
reply.
Secondly, initially i used a temp table to fill the first case and
then updated the temp table with the second case.
now the total number of records are around 50,000 and above, so my
question is will there be a performance (query excute time)
improvement or degradation. In case any furthur details are required
than please do let me know.

Thanks

in case if it there

[Aaron Bertrand [SQL Server MVP]]

Quarantine list with countries and dates

that would exclude a donation at this time.

They are interested in the past 90 days (as Hugo already explained), not a
fixed date range. So, would you have a row for the U.S. for every 90-day
period possible?

[Tom Moreau]

For performance, you can look at indexing. For example, an index on Month
with included columns Value and Indicator may help.

--
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" <da****@gmail.comwrote in message
news:73**********************************@d4g2000p rg.googlegroups.com...
On Mar 24, 6:32 pm, "Tom Moreau" <[e-mail]:

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 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau

"Darsin" <[e-mail] in message

news:8f**********************************@s13g2000 prd.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 help me on this ?

Hi all,

First of all my apologies for using Month, Value, etc keywords in the
example, will remember to not do it again, and thank you for a prompt
reply.
Secondly, initially i used a temp table to fill the first case and
then updated the temp table with the second case.
now the total number of records are around 50,000 and above, so my
question is will there be a performance (query excute time)
improvement or degradation. In case any furthur details are required
than please do let me know.

Thanks

in case if it there

[Anith Sen]

>"Yes/No" is an *answer* to a question. Databases (should) record simple

>facts from which *questions* are constructed. Do you see that concept?

I think you got it backwards. Databases should record simple facts from
which *answers* can be constructed for questions. If "Yes/No" is an answer
to some question, the DBMS must allow for it be recorded so that other
questions can be answered. It is closure, remember?

--
Anith

[Aaron Bertrand [SQL Server MVP]]

the need to constantly update the flags when the underlying facts

changes,

When the flag itself is the fact, this makes very little sense.

[--CELKO--]

>Yes, the term "flag" carries certain baggage from CS history, but I fail to see an argument to avoid a domain with two values, in general. <<

If it is really a domain, I have no problem. I might have problems
with a domain that has only one value, tho. The problem is that a
flag is a computation, predicate, etc. that needs to be derived over
and over from other values. To describe a box, would you store
(length, width, height) then add a column for volume? Of course not
-- it is redundant. Volume by itself would also hide information
about the three variables that went into computing it.

[Hugo Kornelis]

On Wed, 26 Mar 2008 05:19:03 -0000, Tony Rogerson wrote:

>Surely you aren't expecting an answer Hugo?

Hi Tony,

Not really. Joe has a habit of not answering my question when he
realises that he, once more, has cornered himself. This thread proofs
it: he is still replying to Aaron and Anith, but chooses to ignore this
part of the thread.

Ah well. Silence sometimes speaks louder than words... :-)

>I think an example of the form is:
http://www.bloodbook.com/form-donorpre.html

Great example. And in fact quite similar to the one I had to fill out
yesterday, when I went to donate blood (or actually blood plasma).

>I'm really looking forward to seeing celko's answer - perhaps he could use
the link above as a real world requirement.

Surely you aren't expecting an answer Tony?

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

[Aaron Bertrand [SQL Server MVP]]

with a domain that has only one value, tho. The problem is that a

flag is a computation, predicate, etc. that needs to be derived over
and over from other values.

A yes/no question does not have to derived / computed / etc. Do you like
lemonade? Yes. Or no. Maybe your answer changes over time. But it does
not depend on any other values whatsoever.

To describe a box, would you store
(length, width, height) then add a column for volume? Of course not
-- it is redundant. Volume by itself would also hide information
about the three variables that went into computing it.

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!

[Tony Rogerson]

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_clutching (
forum_nntp_guid uniqueidentifier 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' ) ) ) )
)

--
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]" <te*****@dnartreb.noraawrote in message
news:23**********************************@microsof t.com...

>with a domain that has only one value, tho. The problem is that a
flag is a computation, predicate, etc. that needs to be derived over
and over from other values.

A yes/no question does not have to derived / computed / etc. Do you like
lemonade? Yes. Or no. Maybe your answer changes over time. But it does
not depend on any other values whatsoever.

>To describe a box, would you store
(length, width, height) then add a column for volume? Of course not
-- it is redundant. Volume by itself would also hide information
about the three variables that went into computing it.

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!

[Aaron Bertrand [SQL Server MVP]]

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

[Alex Kuznetsov]

On Mar 28, 4:32 pm, --CELKO-- <[e-mail]:

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]

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]" <te*****@dnartreb.noraawrote in message
news:A0**********************************@microsof t.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]

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

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

"Tony Rogerson" <to**********@torver.netwrote in message
news:78**********************************@microsof t.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_clutching (
forum_nntp_guid uniqueidentifier 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--]

>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]]

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--]

>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]]

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]]

>>

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]

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--]

>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]]

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.