Re: Sql Query Help -- Aggregating Rows

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 25 Oct 2007 04:07:21 -0700
Message-ID: <1193310441.726427.283320@57g2000hsv.googlegroups.com>

On Oct 24, 11:13 am, linux.hac..._at_gmail.com wrote:
> I'm having trouble figure out how to do this and thought that if I
> just asked people could show me some best practices and hopefully
> point me to some additional reading material. I've done a bit of sql
> but oracle is new to me (we are using oracle 9.? in case it matters)
>
> Anyways, after joining some tables and ignoring columns I don't care
> about I have a table that looks like:
> PurchaseDate PurchaserID PurchasersCatagory
>
> What they want to do is look at purchases in a three week span this
> year and last year and get a count by purchasers category for 2006
> (excluding those who also bought in 2007), 2007 (excluding...), both
> years
>
> For example:
>
> PurchaseDate PurchaserID PurchaseCatagory
> 2006 1 x
> 2007 1 x
> 2006 2 x
> 2006 3 y
> 2007 4 y
> 2007 5 z
>
> Then they want
> PurchaseCatagory 2006 2007 Both
> x 1 0 1
> y 1 1 0
> z 0 1 0
>
> Thanks,
> Bill

I am having a bit of trouble following the logic, so I will provide a couple tips to help you with the Oracle syntax. Assume that the table name is T1 and that the PURCHASEDATE is declared as a NUMBER column, and you try a simple experiment:
SELECT
PURCHASECATAGORY,

  DECODE(PURCHASEDATE,2006,1,0) IN_2006,
  DECODE(PURCHASEDATE,2007,1,0) IN_2007,
  DECODE(PURCHASEDATE,2006,1,0)*DECODE(PURCHASEDATE,2007,1,0)

IN_2006_2007
FROM
T1;

In the above, we are retrieving:
PURCHASECATAGORY
1 if the PURCHASEDATE is 2006, otherwise a 0 1 if the PURCHASEDATE is 2007, otherwise a 0 1 if the PURCHASEDATE is both 2006 and 2007 (this will always return 0 in this example)

If we then modify the above to group on the PURCHASECATAGORY column, and find the maximum value of the DECODE statements: SELECT
PURCHASECATAGORY,

  MAX(DECODE(PURCHASEDATE,2006,1,0)) IN_2006,
  MAX(DECODE(PURCHASEDATE,2007,1,0)) IN_2007,
  MAX(DECODE(PURCHASEDATE,2006,1,0))*MAX(DECODE(PURCHASEDATE,

2007,1,0)) IN_2006_2007
FROM
  T1
GROUP BY
  PURCHASECATAGORY; In the above, we are retrieving:
  PURCHASECATAGORY
  1 if there is any PURCHASEDATE is 2006 for the PURCHASECATAGORY, otherwise a 0
  1 if there is any PURCHASEDATE is 2007 for the PURCHASECATAGORY, otherwise a 0
  1 if there is any two PURCHASEDATEs have both 2006 and 2007

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Oct 25 2007 - 06:07:21 CDT