On Sun, Nov 15, 2009 at 12:25 PM, Peter Haworth <p...@mollysrevenge.com> wrote: > Thanks for the suggestion. Unfortunately, unless I'm doing something > wrong, this seems to result in just one row being returned showing the > sum amount. >
That is what its supposed to do... a few suggestions -- 1. Don't reply to Digests. The subject line serves no purpose, and breaks the thread. 2. Quote what you are replying to, so it is clearly exactly what you are referring to. For example, looking at your message above, only I can decipher what you are talking about because I wrote the email you are referring to, but because you are not quoting my text, even I have a difficult time trying to figure out what you are saying. We receive hundreds of emails a day, from many mailing lists. Quoting properly (and proper subject lines) helps us keep things in context. 3. There seems to be a lag between what you are reading and what others are sending as responses to you. Several folks have already responded to you on this subject. If you read all those emails, it might help a lot. Good luck. > Pete Haworth > > > On Nov 15, 2009, at 4:00 AM, sqlite-users-requ...@sqlite.org wrote: > >> Date: Sat, 14 Nov 2009 14:17:18 -0600 >> From: P Kishor <punk.k...@gmail.com> >> Subject: Re: [sqlite] Referring to columns named with AS >> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> >> Message-ID: >> <cdf6db500911141217l646bd815s8f2b7f1ad98fc...@mail.gmail.com> >> Content-Type: text/plain; charset=ISO-8859-1 >> >> On Sat, Nov 14, 2009 at 1:58 PM, Peter Haworth >> <p...@mollysrevenge.com> wrote: >>> I'm trying to get a SELECT statement in the following general form >>> to work: >>> >>> SELECT CASE WHEN <condition> THEN <calculation> ELSE <calculation> ? >>> END AS >>> CalcA, sum(CalcA) AS CalcATotal .... >>> >>> I get an error "no such column" referring to CalcA when used in the >>> sum >>> function. ?I'm trying to get total of all the values of CalcA >>> across all the >>> selected rows. ?Is there a way to do this? >>> >> >> Try >> >> SELECT sum(CalcA) AS CalcATotal >> FROM ( >> SELECT CASE WHEN <condition> THEN <calculation> ELSE <calculation> >> END AS CalcA >> FROM table >> WHERE ... >> ) > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, Wisconsin, United States _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users