Complex Query - HELP PLEASE

6 Message(s) by 2 Author(s) originally posted in cold fusion

From: salsaevery

Date: Monday, February 12, 2007

OK, here's my problem.
I need to select distinct(membership_number) from the database

1. <database> One or more large structured sets of persistent
	data, usually associated with software to update and {query}
	the data.  A simple database might be a single file containing
	many {records}, each of which contains the same set of
	{fields} where each field is a certain fixed width.

	A database is one component of a {database management system}.

	See also {ANSI/SPARC Architecture}, {atomic}, {blob}, {data
	definition language}, {deductive database}, {distributed
	database}, {fourth generation language}, {functional
	database}, {object-oriented database}, {relational database}.

	{Carol E. Brown's tutorial
	(http://accounting.rutgers.edu/raw/aies/www.bus.orst.edu/faculty/brownc/lectures/db_tutor/db_tutor.htm)}.

	2. <hypertext> A collection of {nodes} managed and stored in
	one place and all accessible via the same {server}.  {Links}
	outside this are "external", and those inside are "internal".

	On the {World-Wide Web} this is called a {website}.

	3. All the facts and rules comprising a {logic programming}
	program.

	(2005-11-17)

then
sum(amount) each person's various financial contributions.... then I need to
display

1. <hardware> {monitor}.

	2. <language> A vector of pointers to {activation records}.
	The Nth element points to the activation record containing
	variables declared at {lexical depth} N.  This allows faster
	access to variables from outer {scopes} than the alternative
	of linked activation records (but most variable accesses are
	either local or global or occasionally to the immediately
	enclosing scope).  Displays were used in some {ALGOL}
	implementations.

	(1996-02-22)

these contribution totals in order descending. Sounds simple, and
it should be. But I can not figure it out.

What's the secret? I have tried arrays, lists

<data> A data structure holding many values, possibly of
	different types, which is usually accessed sequentially,
	working from the head to the end of the tail - an "ordered
	list".  This contrasts with a (one-dimensional) {array}, any
	element of which can be accessed equally quickly.

	Lists are often stored using a cell and pointer arrangement
	where each value is stored in a cell along with an associated
	pointer to the next cell.  A special pointer, e.g. zero, marks
	the end of the list.  This is known as a (singly) "linked
	list".  A doubly linked list has pointers from each cell to
	both next and previous cells.

	An unordered list is a {set}.

	(1998-11-12)

and query

1. <database, information science> A user's (or {agent}'s)
	request for information, generally as a formal request to a
	{database} or {search engine}.

	{SQL} is the most common {database query language}.

	2. <character> {question mark}.

	(1997-04-09)

ing a query. I can not
get it to work.

Thanks for your help.
Rob

From: salsaevery

Date: Tuesday, February 13, 2007

Sorry, Microsoft

<company> The biggest supplier of {operating systems} and
	other software for {IBM PC} compatibles.  Software products
	include {MS-DOS}, {Microsoft Windows}, {Windows NT},
	{Microsoft Access}, {LAN Manager}, {MS Client}, {SQL Server},
	{Open Data Base Connectivity} (ODBC), {MS Mail}, and {SNA
	Server} for Windows NT.

	Microsoft was founded as "Micro-soft" in 1975 by {Bill Gates}
	(now CEO) and his high school pal Paul Allen.  Their first
	product was a version of {BASIC} for the new {Altair} computer
	[which one?].  In 1980, {IBM} chose Microsoft to supply the
	{operating system} for the {IBM PC}.

	On the UK television program "The Net" in May 1994, {Bill
	Gates} said he was betting his company on the {information
	highway}".

	Quarterly sales $1293M, profits $362M (Aug 1994).

	{Home (http://www.microsoft.com/)}.
	{(ftp://ftp.microsoft.com/)}.

	{Interesting Info and Other Microsoft WWW Servers
	(http://www-drg.microsoft.com/devinfo.htm)}.

	{Microsoft Windows Developer Information
	(http://www-drg.microsoft.com/devinfo.htm)}.

	{Microsoft Research Group Information
	(http://www.research.microsoft.com)}.

	{Win_News
	(http://www.microsoft.com/chicago/ms-www/ms-intro.htm)}.
	maintained by the Personal Operating Systems Division to
	distribute information on {Microsoft Windows}, {MS-DOS} and
	{Windows 95}.

	(1998-11-06)

Access

1. <language> An English-like query language used in the
	{Pick} {operating system}.

	2. <database, product> {Microsoft Access}.

	(1994-11-08)

Database and Cold Fusion 6.1

I will give your solution

<jargon> A {marketroid} term for something he wants to sell
	you without bothering you with the often dizzying distinctions
	between {hardware}, {software}, {services}, {applications},
	{file formats}, companies, brand names and {operating
	systems}.

	"{Flash} is a perfect image-streaming solution."  "What is
	it?"  "Um...  about a thousand dollars."

	See also: {technology}.

	(1998-07-07)

a try.... I have not used that formula

1. In logic, a sequence of symbols representing terms,
	{predicates}, {connectives} and {quantifiers} which is either
	true or false.

	2. <language, music> FORTH Music Language.  An extension of
	{FORTH} with concurrent note-playing processes.  Runs on
	{Macintosh} and {Atari ST} with {MIDI} output.

	["Formula: A Programming Language for Expressive Computer
	Music", D.P. Anderson et al Computer 24(7):12 (Jul 1991)].

	3. Preprocessor language for the {Acorn Archimedes}, allowing
	inline high-level statements to be entered in an assembly
	program.  Written in {nawk}.

yet.

wrote in message

In {object-oriented programming} sending a message to an
	{object} (to invoke a {method}) is equivalent to calling a
	{procedure} in traditional programming languages, except that
	the actual code executed may only be selected at run time
	depending on the {class} of the object.  Thus, in response to
	the message "drawSelf", the method code invoked would be
	different if the target object were a circle or a square.

	(1995-02-16)

I need to select distinct(membership_number) from the database then
sum(amount) each person's various financial contributions.... then I need
to
display these contribution totals in order descending. Sounds simple,
and
it should be. But I can not figure it out.
It's a little hard to give an answer without knowing the data
schema. Also, what database server

1. A program which provides some service to other ({client})
	programs.  The connection between client and server is
	normally by means of {message passing}, often over a
	{network}, and uses some {protocol} to encode the client's
	requests and the server's responses.  The server may run
	continuously (as a {daemon}), waiting for requests to arrive
	or it may be invoked by some higher level daemon which
	controls a number of specific servers ({inetd} on {Unix}).

	There are many servers associated with the {Internet}, such as
	those for {HTTP}, {Network File System}, {Network Information
	Service} (NIS), {Domain Name System} (DNS), {FTP}, {news},
	{finger}, {Network Time Protocol}.  On Unix, a long list can
	be found in /etc/services or in the {NIS} database "services".
	See {client-server}.

	2. A computer which provides some service for other computers
	connected to it via a network.  The most common example is a
	{file server} which has a local disk and services requests
	from remote clients to read and write files on that disk,
	often using {Sun}'s {Network File System} (NFS) {protocol} or
	{Novell Netware} on {PCs}.  Another common example is a {web
	server}.

	[{Jargon File}]

	(2003-12-29)

are you using?
In Oracle I'd start with something like:
select
membership_number,
sum(amount)
from
table_name
order by
sum(amount) desc
group

A group G is a non-empty {set} upon which a {binary} operator
	* is defined with the following properties for all a,b,c in G:

	  Closure:     G is closed under *,  a*b in G
	  Associative: * is associative on G, (a*b)*c = a*(b*c)
	  Identity:    There is an identity element  e  such that
		       a*e = e*a = a.
	  Inverse:     Every element has a unique inverse a' such that
		       a * a' = a' * a = e.  The inverse is usually
		       written with a superscript -1.

	(1998-10-03)

by
membership_number
--

> Bob

David Betz.  A tiny object-oriented language.

	{(ftp://ftp.mv.com/pub/ddj/packages/bob15.arc)}.

	[Dr Dobbs J, Sep 1991, p.26].

Dively | I used to be indecisive, but now I'm not sure...

From: Bob Dively

Date: Tuesday, February 13, 2007

wrote in message:

I need to select distinct(membership_number) from the database then
sum(amount) each person's various financial contributions.... then I need to
display these contribution totals in order descending. Sounds simple, and
it should be. But I can not figure it out.

It's a little hard to give an answer without knowing the data
schema. Also, what database server are you using?

In Oracle I'd start with something like:

select
membership_number,
sum(amount)
from
table_name
order by
sum(amount) desc
group by
membership_number

--
Bob Dively | I used to be indecisive, but now I'm not sure...

From: salsaevery

Date: Tuesday, February 13, 2007

No good.
I can not get rid of this message when I run

<operating system, programming> The process of carrying out
	the {instructions} in a computer program by a computer.

	See also {dry run}.

	(1996-05-13)

your query...... I have tried
re-working it too with no luck.

"Syntax error

1. A discrepancy between a computed, observed, or measured
	value or condition and the true, specified, or theoretically
	correct value or condition.

	2. <programming> A mental mistake made by a programmer that
	may result in a program {fault}.

	3. (verb) What a program does when it stops as result of a
	programming error.

	(2000-03-28)

(missing operator) in query expression

<programming> Any piece of program code in a {high-level
	language} which, when (if) its execution terminates, returns a
	value.  In most programming languages, expressions consist of
	constants, variables, operators, functions, and {parentheses}.
	The operators and functions may be built-in or user defined.
	Languages differ on how expressions of different {types} may
	be combined - with some combination of explicit {casts} and
	implicit {coercions}.

	The {syntax} of expressions generally follows conventional
	mathematical notation, though some languages such as {Lisp} or
	{Forth} have their own idiosyncratic syntax.

	(2001-05-14)

'sum(amount) group by
memberid'."

Here's my query statement.
<CFQUERY datasource="#application2.dsn#" name="getinfo">
select sum(amount) memberid
from donations
order by sum(amount)
group by memberid
</CFQUERY>Have any thoughts?

Thanks

wrote in message:
I need to select distinct(membership_number) from the database then
sum(amount) each person's various financial contributions.... then I need
to
display these contribution totals in order descending. Sounds simple,
and
it should be. But I can not figure it out.
It's a little hard to give an answer without knowing the data
schema. Also, what database server are you using?
In Oracle I'd start with something like:
select
membership_number,
sum(amount)
from
table_name
order by
sum(amount) desc
group by
membership_number
--

> Bob Dively | I used to be indecisive, but now I'm not sure...

From: Bob Dively

Date: Tuesday, February 13, 2007

wrote in message:

Here's my query statement.
<CFQUERY datasource="#application2.dsn#" name="getinfo">
select sum(amount) memberid
from donations
order by sum(amount)
group by memberid
</CFQUERY>

Sorry, I got the order by and group by clauses backwards.

Try this (it works, just tested in Access):

SELECT Sum(amount), memberid
FROM donations
GROUP BY memberid
ORDER BY Sum(amount) DESC;

--
Bob Dively | I used to be indecisive, but now I'm not sure...

From: salsaevery

Date: Tuesday, February 13, 2007

Wow, why did not I try that?

That worked perfectly. Exactly what I was hoping for.
Thank you so much for the help.

wrote in message:

Here's my query statement.
<CFQUERY datasource="#application2.dsn#" name="getinfo">
select sum(amount) memberid
from donations
order by sum(amount)
group by memberid
</CFQUERY>
Sorry, I got the order by and group by clauses backwards.
Try this (it works, just tested in Access):
SELECT Sum(amount), memberid
FROM donations
GROUP BY memberid
ORDER BY Sum(amount) DESC;
--

> Bob Dively | I used to be indecisive, but now I'm not sure...

Next Message: CF 7 list box connections

Blogs related to Complex Query - HELP PLEASE

Reiki Healing Love providing Distant Energy Spiritual (Healing ...
Please view this importantDivorceCare is a divorce recovery support group where you ... including update queries, action queries, and the Query Wizards Building forms ... The public Beta 2 of the ColdFusion/Flex Connectivity kit is now ...

How I create an application
The project plan can be simple to complex, but the more effort you put into it, for the simpler ... please add them to the top section with all the cfparams. ... A. Use a sql tool to generate the data, you'll need for each query, ...

Re: Consuming ColdFusion Webservice with .NET
NET and ColdFusion based services? Any > pointers? Yep...if you return a query from your CF based webservice .NET will choke on what's called a QueryBean. Matt Small on-list wrote a DLL for .NET to help it past this issue. ...

What You Need to Know About Web Application Development
An application as complex as the proposed new CGD Web site can only truly be ... the application will need to query numerous different database tables and ... Most languages such as Cold Fusion, PHP and ASP parse the code line by line. ...

Résumé and Cover Letter Secrets Revealed!
The data you assemble will help you write your Guaranteed Résumé (in the ... Please consider my following abilities: • Analyze and solve complex problems. ... FastTrack to ColdFusion training offered by Allaire, New York, NY (2000). ...

Building your first Ajax application with PHP
NB - in making your own functions like this, please note ... This is simply ajax.php, plus the query mark ?, then name=. ... Armed with this knowledge you should now be able to adapt this code to do something maybe a bit more complex. ...