pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: will trillich <will@serensoft.com>
To: pgsql-general@postgresql.org
Subject: Re: sql question
Date: Wed, 16 May 2001 00:26:13 -0500
Message-ID: <20010516002613.M29095@serensoft.com> (raw)
In-Reply-To: <l.989779505.1960693359@[212.171.170.132]>
References: <l.989779505.1960693359@[212.171.170.132]>

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#    &brvbar;   Ordertype  &brvbar; Date
> 1           &brvbar;   A          &brvbar; April
> 1           &brvbar;   B          &brvbar; May
> 1           &brvbar;   B          &brvbar; May
> 2           &brvbar;   B          &brvbar; April
> 2           &brvbar;   B          &brvbar; May
> 2           &brvbar;   C          &brvbar; May
> 3           &brvbar;   C          &brvbar; 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 &brvbar; TotA  &brvbar; TotB  &brvbar; TotC  &brvbar; Total
> 1       &brvbar; 0     &brvbar; 2     &brvbar; 0     &brvbar; 2
> 2       &brvbar; 0     &brvbar; 1     &brvbar; 1     &brvbar; 2
> 3       &brvbar; 0     &brvbar; 0     &brvbar; 1     &brvbar; 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!



view thread (71+ messages)  latest in thread

Message-ID: <20010516002613.M29095@serensoft.com>
Permalink:  ../20010516002613.M29095@serensoft.com/
Also on:    postgresql.org/message-id/20010516002613.M29095@serensoft.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: will@serensoft.com
  Subject: Re: sql question
  In-Reply-To: <20010516002613.M29095@serensoft.com>
  References: <l.989779505.1960693359@[212.171.170.132]> <20010516002613.M29095@serensoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox