Re: sql question

On Wed, 16 May 2001, will trillich wrote:

> 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
>
> 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.

You can do it in SQL, with something like:

SELECT accountnum, (SELECT count(*) FROM data a WHERE a.accounttype='a'
and a.accountnum=o.accountnum) AS TotA, .. < same for B and C > .. FROM
data o group by accoutnum;

It won't be terribly fast, but, then, getting all this data into Python or
Perl and doing it there won't be so speedy either.

HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington