Nick Wilczynski - All Comments

re: SUM(Case) in SQL

Emanuel — Wed, 15 Jul 2009 17:00:35 GMT

Great explanation and examples for a novice like me.

Thanks.

re: SUM(Case) in SQL

RickPetersen — Wed, 29 Apr 2009 18:51:21 GMT

I think you're missing a part of how case works... it's a case structure used specifically so you don't have to nest conditional logic (if statements, et all). Your query rewritten like below should work (except for anything I fat-finger):

SELECT SUM(CASE WHEN rm40401.docabrev = 'CR' THEN -rm20101.CURTRXAM

WHEN rm40401.docabrev = 'PMT' THEN -rm20101.CURTRXAM

WHEN rm40401.docabrev = 'RTN' THEN -rm20101.CURTRXAM

ELSE rm20101.CURTRXAM END)

AS AMOUNT,

RM20101.CUSTNMBR, RM00101.CUSTNAME, RM40401.DOCABREV

FROM RM20101 INNER JOIN

RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN

RM40401 ON RM20101.RMDTYPAL = RM40401.RMDTYPAL

Or even more concisely

SELECT SUM(CASE WHEN rm40401.docabrev in ('CR','PMT','RTN') THEN -rm20101.CURTRXAM

ELSE rm20101.CURTRXAM END)

AS AMOUNT,

RM20101.CUSTNMBR, RM00101.CUSTNAME, RM40401.DOCABREV

FROM RM20101 INNER JOIN

RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN

RM40401 ON RM20101.RMDTYPAL = RM40401.RMDTYPAL

re: SUM(Case) in SQL

SeibGuy — Wed, 25 Mar 2009 02:44:25 GMT

So why can't I:

SELECT SUM(CASE WHEN rm40401.docabrev = 'CR' THEN - rm20101.CURTRXAM ELSE (CASE WHEN rm40401.docabrev = 'PMT' THEN - rm20101.CURTRXAM ELSE

(CASE WHEN rm40401.docabrev = 'RTN' THEN - rm20101.CURTRXAM ELSE rm20101.CURTRXAM END) END) END) AS AMOUNT,

RM20101.CUSTNMBR, RM00101.CUSTNAME, RM40401.DOCABREV

FROM RM20101 INNER JOIN

RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN

RM40401 ON RM20101.RMDTYPAL = RM40401.RMDTYPAL

re: SUM(Case) in SQL

irenilda — Fri, 13 Mar 2009 12:09:16 GMT

Legal serviu muito bem para mim, faça um exemplo para converter um varchar dentro do case pois eu não sei como colocar valeu

re: SUM(Case) in SQL

Geek Bully — Fri, 06 Feb 2009 21:02:48 GMT

Brilliant! This statement rocks. Thank you! Thank you!

To the new weblogs.asp.net bloggers

.Net World — Mon, 14 Apr 2008 21:21:45 GMT

First of all: welcome. Now, as you all might know, this blog site, http://weblogs.asp.net , has a grouped

Drinking from the, erm, garden hose?

Dave Transom's C# Vitamins — Wed, 06 Feb 2008 06:10:18 GMT

Drinking from the, erm, garden hose?

To the new weblogs.asp.net bloggers

Community Blogs — Tue, 29 Jan 2008 09:43:51 GMT

First of all: welcome. Now, as you all might know, this blog site, http://weblogs.asp.net , has a grouped

re: Poor Code

panjkov — Tue, 29 Jan 2008 09:32:19 GMT

When I read Joe's announcement on opening registration on this site, I was in doubt "should I move or not" and I decided not to move on asp.net weblogs. Back in 2004 and 2005 it was almost impossible to get a blog on this site,and then was so temptating :) Now, door is completely open and it was not so temptating... On the other hand, in my posting cycle of 1-2 posts per month I cannot guarantee it is worth moving my blog. I aggree that there is now much more worthless content than before.

re: Poor Code

FransBouma — Tue, 29 Jan 2008 08:59:05 GMT

I agree. THe floodgates have opened. I don't mind people having a blog here, but please... some guys are posting links to articles from the stone age, small posts with tips that are at page 1 of the manual etc...

People who are new: don't rehash links to articles which are stone old, and don't post lists of tips which are wrong. Thanks.