Re: sql question

On Wed, 16 May 2001, will trillich wrote:

> On Sun, May 13, 2001 at 06:45:05PM +0000, u95886230(at)spawnkill(dot)ip-mobilphone(dot)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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington