Hi everybody!

I am trying to produce following report: sum of sales value for two years
group by months:

month 2006 2007
------- ------ --------
Jan 111.0 332.00
Feb 222.0 225.00
March 232.0 234.00
.................................
Dec 322.00 344.00

Here is my query for this report:

WITH T1(SALESDATE,SALESAMOUNT,YEAR,MONTH,MONTHNAME)
AS (SELECT MONTHEND,SALEVOL,YEAR(MONTHEND),MONTH(MONTHEND),MO NTHNAME
(MONTHEND) FROM MYSALES
ORDER BY 3,4)
Select coalesce(y1.mm,y2.mm) AS MM,coalesce(y1.mth,y2.mth) AS MONTH_NAME,
sum(y1.amt) as
YEAR_2001,sum(y2.amt) as YEAR_2002
from (select month as mm, monthname as mth, sum(SalesAmount) as amt
from T1
WHERE year = 2006
group by month, monthname) as y1
full outer join
(select month as mm, monthname as mth,sum(SalesAmount) as amt
from T1
WHERE year = 2007
group by month, monthname) as y2
on y2.mth = y1.mth
order by coalesce(y1.mm,y2.mm);

But i got the following error:
sqlcode: -119
An expression starting with "MONTH_NAME specified in a SELECT
clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY
clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a
column function and no GROUP BY clause is specified. SQLSTATE=4280

Any idea what is wrong with this query?
Thank's in advance. Leny G.

--
Message posted via http://www.dbmonster.com

The error -119 may be solved by adding "GROUP BY
coalesce(y1.mm,y2.mm),coalesce(y1.mth,y2.mth)".
But, looking into your required output, it must be not neccesary to
include "coalesce(y1.mm,y2.mm) AS MM "in SELECT list.

Here is an example:
WITH T1(SALESAMOUNT,YEAR,MONTH,MONTHNAME)
AS (SELECT SALEVOL,YEAR(MONTHEND),
MONTH(MONTHEND),SUBSTR(MONTHNAME (MONTHEND),1,12)
FROM MYSALES
)
Select coalesce(y1.mth,y2.mth) AS "month",
sum(y1.amt) as "2006", sum(y2.amt) as "2007"
from (select month as mm, monthname as mth, sum(SalesAmount) as amt
from T1
WHERE year = 2006
group by month, monthname) as y1
full outer join
(select month as mm, monthname as mth,sum(SalesAmount) as amt
from T1
WHERE year = 2007
group by month, monthname) as y2
on y2.mth = y1.mth
GROUP BY coalesce(y1.mm,y2.mm),coalesce(y1.mth,y2.mth)
order by coalesce(y1.mm,y2.mm)
;

Thank You very much Tonkuma for your help it is working perfect:
Here is the output after your fix:

month 2006($mln) 2007($mln)
------------ ------------------- ------------------
January 337 354
February 366 409
March 431 437
April 379 412
May 419 481
June 425 403
July 397 462
August 421 465
September 434 417
October 441 405
November 455 407
December 406 380

12 record(s) selected.

One more question: T1 table data for 10 years from 1998 thru 2007.
Is there a generic way to build such report without adding 8 more outer joins
to existing query?
Thank's in advance Leny G.


Tonkuma wrote:
>The error -119 may be solved by adding "GROUP BY
>coalesce(y1.mm,y2.mm),coalesce(y1.mth,y2.mth)".
>But, looking into your required output, it must be not neccesary to
>include "coalesce(y1.mm,y2.mm) AS MM "in SELECT list.
>
>Here is an example:
>WITH T1(SALESAMOUNT,YEAR,MONTH,MONTHNAME)
> AS (SELECT SALEVOL,YEAR(MONTHEND),
> MONTH(MONTHEND),SUBSTR(MONTHNAME (MONTHEND),1,12)
> FROM MYSALES
> )
>Select coalesce(y1.mth,y2.mth) AS "month",
> sum(y1.amt) as "2006", sum(y2.amt) as "2007"
> from (select month as mm, monthname as mth, sum(SalesAmount) as amt
> from T1
> WHERE year = 2006
> group by month, monthname) as y1
> full outer join
> (select month as mm, monthname as mth,sum(SalesAmount) as amt
> from T1
> WHERE year = 2007
> group by month, monthname) as y2
> on y2.mth = y1.mth
> GROUP BY coalesce(y1.mm,y2.mm),coalesce(y1.mth,y2.mth)
> order by coalesce(y1.mm,y2.mm)
>;

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1

On May 27, 9:48 pm, "lenygold via DBMonster.com" <u41482@uwe> wrote:
> Thank You very much Tonkuma for your help it is working perfect:
> Here is the output after your fix:
>
> month 2006($mln) 2007($mln)
> ------------ ------------------- ------------------
> January 337 354
> February 366 409
> March 431 437
> April 379 412
> May 419 481
> June 425 403
> July 397 462
> August 421 465
> September 434 417
> October 441 405
> November 455 407
> December 406 380
>
> 12 record(s) selected.
>
> One more question: T1 table data for 10 years from 1998 thru 2007.
> Is there a generic way to build such report without adding 8 more outer joins
> to existing query?
> Thank's in advance Leny G.
>
>
>
> Tonkuma wrote:
> >The error -119 may be solved by adding "GROUP BY
> >coalesce(y1.mm,y2.mm),coalesce(y1.mth,y2.mth)".
> >But, looking into your required output, it must be not neccesary to
> >include "coalesce(y1.mm,y2.mm) AS MM "in SELECT list.
>
> >Here is an example:
> >WITH T1(SALESAMOUNT,YEAR,MONTH,MONTHNAME)
> > AS (SELECT SALEVOL,YEAR(MONTHEND),
> > MONTH(MONTHEND),SUBSTR(MONTHNAME (MONTHEND),1,12)
> > FROM MYSALES
> > )
> >Select coalesce(y1.mth,y2.mth) AS "month",
> > sum(y1.amt) as "2006", sum(y2.amt) as "2007"
> > from (select month as mm, monthname as mth, sum(SalesAmount) as amt
> > from T1
> > WHERE year = 2006
> > group by month, monthname) as y1
> > full outer join
> > (select month as mm, monthname as mth,sum(SalesAmount) as amt
> > from T1
> > WHERE year = 2007
> > group by month, monthname) as y2
> > on y2.mth = y1.mth
> > GROUP BY coalesce(y1.mm,y2.mm),coalesce(y1.mth,y2.mth)
> > order by coalesce(y1.mm,y2.mm)
> >;
>

IMO sql is not the right tool to do presentation, but perhaps you can
use something like:

SELECT MONTH(MONTHEND),
sum(case when YEAR(MONTHEND) = 1997 then SALEVOL else 0
end) as Sum1997,
sum(case when YEAR(MONTHEND) = 1998 then SALEVOL else 0
end) as Sum1998,
[...]
FROM MYSALES

/Lennart