Using Group By with a scalar function or to find shorter way of coding this query

[Skips]

Hi,

I am trying to create a query where I can retrieve average monthly balances for a client for all the months that we have data stored for the year ending 2007.

The table has a balance, for every business day of each month (AIGP_ENTRY_D) +- 24 entries per month. The result I need is a list of the month and the average balance for that month e.g.

MNTH              AVG_BAL
-----             ---------------
 01                  1000.00
 02                  1200.00
 03                   900.00
 04                   300.00

The only way I can get the average balance is to use the 'union all' up to 11 times, and to hard code the month.

SELECT AVG(AIGP_GRP_CR_BAL_A )  AS AVG_BAL
FROM CATS.AIA_GRP                                 
WHERE AIGP_GRP_C = '00160'                        
AND YEAR(AIGP_ENTRY_D) = 2007                     
AND MONTH(AIGP_ENTRY_D) = 1     
 
     UNION ALL                                         
 
SELECT AVG(AIGP_GRP_CR_BAL_A )  AS AVG_BAL
FROM CATS.AIA_GRP                                 
WHERE AIGP_GRP_C = '00160'                        
AND YEAR(AIGP_ENTRY_D) = 2007                     
AND MONTH(AIGP_ENTRY_D) = 2           
 
    UNION ALL       
...

I have tried this:

SELECT MONTH(AIGP_ENTRY_D)      AS MNTH,
       AVG(AIGP_GRP_CR_BAL_A)      AS AVG_BAL
FROM CATS.AIA_GRP 
WHERE AIGP_GRP_C = '00160'
AND YEAR(AIGP_ENTRY_D) = 2007
GROUP BY MNTH

I get the following error message:

SQLCODE -206
MNTH IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE,
 

There has to be a shorter way to do this in DB2.

[sakumar9]

One possible solution:

SELECT AVG(AIGP_GRP_CR_BAL_A ) AS AVG_BAL
FROM CATS.AIA_GRP
WHERE AIGP_GRP_C = '00160'
AND YEAR(AIGP_ENTRY_D) = 2007
AND MONTH(AIGP_ENTRY_D) IN (1,2,3,4,5,6,7,8,9,10,11) 

Let me know if this is what you were looking for.

Regards
-- Sanjay

[docdiesel]

Hi,

first, I added some code tags to your posts for better readability. Second, I'd do it in two steps, using a temporary table:

SELECT
  tmptab.month as month,
  avg(tmptab.balance) as avg_balance
FROM
(
  SELECT
    Month(AIGP_ENTRY_D) as month,
    AIGP_GRP_CR_BAL_A as balance
  FROM
    CATS.AIA_GRP
  WHERE
    YEAR(AIGP_ENTRY_D) = 2007
) tmptab
GROUP BY
  tmptab.month
ORDER BY
  tmptab.month ASC
;
 

Regards,

Bernd