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 ?
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 help 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 help me on this ?
|||One way:
SELECT Month,
SUM(CASE WHEN indicator = 1 THEN Value ELSE 0 END) AS sum1,
SUM(CASE WHEN indicator = 0 THEN Value ELSE 0 END) AS sum0
FROM TABLE1
GROUP BY Month
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||> 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.
[vbcol=seagreen]
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 thatyou 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.
[vbcol=seagreen]
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.
|||>> "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.
|||On Mar 24, 6:32Xpm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Try:
> SELECT
> X X Month
> , XSUM(CASE WHEN indicator = 0 THEN Value ELSE 0 END) Value0
> , XSUM(CASE WHEN indicator = 1 THEN Value ELSE 0 END) Value1
> FROM TABLE1
> GROUP BY Month
> --
> X XTom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON X Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Darsin" <dar...@.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 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
|||> They don't care about your trips to AIDS infected Africa? Avian flu
> in China? Mad Cow Disease in the UK?
No, Joe. Have you ever donated blood in the U.S.? I have donated just
under 3 gallons and I have never been asked about this stuff. There are a
few yes / no questions about certain countries and certain time periods.
There is also a yes / no question about whether I have ever exchanged sex
for money. On that there is not a request for a list of transactions and/or
acts/positions, either. Just a checkbox. I'm sure you would prefer all the
sordid details, but alas, donors are expected to take about 10 minutes to
fill out the questionnaire; it is not a take-home exam with essay questions
and bonus points.
> 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.
But Joe, different states have different organizations that collect blood...
some multiple. So, I could have donated to some blood donor trailer in
Arkansas yesterday, and for some reason want to donate again today in
Boston. Since they can't check every single blood donor organization in the
world, they ask... "have you given blood in the past 90 days?" (They don't
bother asking "have you given blood to this organization in the past 90
days?" because that is something they can verify on their own.) What you
are expecting is like going to the equivalent of the DMV in Tehran or Beirut
or Cairo and expecting them to be able to print you up a new Rhode Island
driver's license.
> Then I would ask "what countries have you visited since << insert
> date>>?"
They do ask that... "what countries have you visited in the past 36 months?"
is the one in my state. But they also want to know about your presence in
certain countries in certain periods of time, e.g. Congo between 1978 and
1982, etc. Now do you really think they are going to leave an open-ended
essay question, require a certified copy of your travel itinerary, etc.? Of
course not. They are relying on you to be honest and check yes / no. They
are going to take your blood and test it anyway, but this kind of screening
is a very simple way to prevent that blood from being taken and having to be
tested.
Does the concept of only needing a yes / no answer really escape you this
much? You are a clever person, and I have to assume this is all for show.
A
No comments:
Post a Comment