Thread: sql question

sql question

From
u95886230@spawnkill.ip-mobilphone.net
Date:
Please help, I am trying to write an SQL statement but with no success as I am just
starting out with sql.

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

thanks
sgebbie@ciaoweb.it




--
Sent by sgebbie from  ciaoweb piece from  it
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new


Re: sql question

From
Justin Clift
Date:
Hi,

I don't know if it's useful, but there are a number of SQL tutorials
available at
http://www.newbienetwork.net/sections.php?op=listarticles&secid=5 and
there is a list of all sorts of useful PostgreSQL & SQL resources
(including tutorials) at http://techdocs.postgresql.org/oresources.php

:-)

Regards and best wishes,

Justin Clift

u95886230@spawnkill.ip-mobilphone.net wrote:
>
> Please help, I am trying to write an SQL statement but with no success as I am just
> starting out with sql.
>
> 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
>
> thanks
> sgebbie@ciaoweb.it
>
>
> --
> Sent by sgebbie from  ciaoweb piece from  it
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi

Re: sql question

From
will trillich
Date:
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!

Re: sql question

From
Joel Burton
Date:
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


Re: sql question

From
"Blackin"
Date:
select sum(case when ordertype='A' then 1 else 0 end) as TotA,
sum(case when ordertype='B' then 1 else 0 end) as TotB,
sum(case when ordertype='C' then 1 else 0 end) as TotC
From Thetable
where Date="May"

This works if you know how many columns(OrderTypes) that you have.  If you
do not know this query can be built dynamically.

Rob


<u95886230@spawnkill.ip-mobilphone.net> wrote in message
news:l.989779505.1960693359@[212.171.170.132]...
> Please help, I am trying to write an SQL statement but with no success as
I am just
> starting out with sql.
>
> 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
>
> thanks
> sgebbie@ciaoweb.it
>
>
>
>
> --
> Sent by sgebbie from  ciaoweb piece from  it
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new