You might also want to add: GROUP BY state:
SELECT State=5,+
SUM(IFEQ(SaleType,'CHK',Amount,0))=9=S as CHK,
SUM(IFEQ(SaleType,'CHRG',Amount,0))=9=S as CHRG,
SUM(Amount)=9=S as Total +
FROM Charges +
GROUP BY state
Randy Peterson
dennis wrote:
> We want a crosstab which shows:
>
> State CHK CHRG Total
> IL 500 0 500
> KY 0 100 100
> MA 400 300 700
> --------------------------------
> Total 900 400 1300
>
> SELECT State=5,+
> SUM(IFEQ(SaleType,'CHK',Amount,0))=9=S as CHK,
> SUM(IFEQ(SaleType,'CHRG',Amount,0))=9=S as CHRG,
> SUM(Amount)=9=S as Total +
> FROM Charges
>
> This will give you almost the same output as the above crosstab!!!!
>
> -- Dennis McGrath
> mailto:[EMAIL PROTECTED]
>
> -- Productivity Tools for R:Base Programmers
> http://www.enteract.com/~mcgrath/dennis
>
> -- Full time consultant with:
> SQL Resources Group
> Steve Hartmann
> Oak Park, IL
> mailto:[EMAIL PROTECTED]
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of david blocker
> Sent: Tuesday, August 07, 2001 2:29 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Saving the crosstab results as a table or view
>
> Actually, there's an even better way to do crosstabs with SELECT. Suppose
> for example, you have the following table Charges:
>
> State SaleType Amount
>
> MA CHRG 100
> MA CHRG 200
> MA CHK 400
> IL CHK 300
> IL CHK 200
> KY CHRG 100
>
> We want a crosstab which shows:
>
> State CHK CHRG Total
> IL 500 0 500
> KY 0 100 100
> MA 400 300 700
> --------------------------------
> Total 900 400 1300
>
> Here's the SELECT:
>
> SELECT State,+
> SUM(IFEQ(SaleType,'CHK',Amount,0)),
> SUM(IFEQ(SaleType,'CHRG',Amount,0)) +
> FROM Charges
>
> Love those logical functions!
>
> David Blocker
>
> Total
> ----- Original Message -----
> From: "Jim Blackburn" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, August 07, 2001 12:40 PM
> Subject: Re: Saving the crosstab results as a table or view
>
> > rich wrote:
> > >
> > > Is there a way to save the results of a crosstab as as table or view?
> > >
> > > rich
> > The only way is to output to a file then load that file to a table.
> >
> > But, you can get the same information, organized differently:
> > cross sum ClmA for ClmB by ClmC FROM Table
> >
> > vs:
> > select ClmB, ClmC, sum(ClmA) from Table GROUP BY ClmB, ClmC
> >
> > the latter provides the same information, organized in a way that it will
> go directly into a table if you wish, or can be set up as a view. Since the
> latter form follows the relational model, it is easier to use for further
> processing.
> >
> > Jim Blackburn
> > Kodiak
