Received: from server (client-sp03-85.speedex.net [208.33.90.85]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4G5QEA54396 for ; Wed, 16 May 2001 01:26:14 -0400 (EDT) (envelope-from will@serensoft.com) Received: from will by server with local (Exim 3.12 #1 (Debian)) id 14ztp7-0008AQ-00 for ; Wed, 16 May 2001 00:26:13 -0500 Date: Wed, 16 May 2001 00:26:13 -0500 From: will trillich To: pgsql-general@postgresql.org Subject: Re: sql question Message-ID: <20010516002613.M29095@serensoft.com> Mail-Followup-To: pgsql-general@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: ; from u95886230@spawnkill.ip-mobilphone.net on Sun, May 13, 2001 at 06:45:05PM +0000 X-Archive-Number: 200105/698 X-Sequence-Number: 9572 On Sun, May 13, 2001 at 06:45:05PM +0000, u95886230@spawnkill.ip-mobilphone.net wrote: > I have a table with 3 columns: Account# ,OrderType and date > example of data: > Account# ¦ Ordertype ¦ Date > 1 ¦ A ¦ April > 1 ¦ B ¦ May > 1 ¦ B ¦ May > 2 ¦ B ¦ April > 2 ¦ B ¦ May > 2 ¦ C ¦ May > 3 ¦ C ¦ May > > > I need to write a select that will show me the totals of EACH type for EACH account AND > total ordersplaced for a SPECIFIC month eg..Show me the results for May... > > account ¦ TotA ¦ TotB ¦ TotC ¦ Total > 1 ¦ 0 ¦ 2 ¦ 0 ¦ 2 > 2 ¦ 0 ¦ 1 ¦ 1 ¦ 2 > 3 ¦ 0 ¦ 0 ¦ 1 ¦ 1 > > I can use temp tables, but need a solution written as basic as pssible so I can understand > it (all in the form select this from that) > any help would be fantastic as I am completely stuck and have been trying for about a week don't you hate that? :) sql is not like perl or basic in that you can start this over here, assign a variable, break your output into chunks and do various voodoo to it before it comes out. all sql does is ask for records from a database, to return them in a certain order or in certain groups. select city,state,count(id),sum(id) from mycontacts group by state,city; that'll show you something like chicago il 12 345 evanston il 3 121 springfield il 9 208 evansville in 5 184 jasper in 4 166 terre haute in 6 201 henderson ky 5 190 louisville ky 8 124 if you're pulling those results into a program (php? perl?) then you can do interesting things such as output a break on state boundaries to compute state subtotals, and so forth. of course, there are devious individuals out there who don't know how to play by the rules and they make sql do some amazing handsprings. but those people are not much fun at parties. (and seldom attend, anyway.) -- What do I need manners for? I already got me a wife. -- Adam Pontipee, "Seven Brides for Seven Brothers" will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!