SQL SELECT with GROUP BY

[login to unmask email]

SQL SELECT with GROUP BY
In the following SQL query

SELECT Product, Year(Order_Date) as Year_Ordered, Sum(Cost_Amount)
as Total
FROM PRODUCT_ORDERS
GROUP BY Product, ? <-- After Product, I would like to group by the
Order_Date year only.

I know that an inline view could be used as well as a traditional view
definition. By the way, I am already coding this as an inline view where
the columns selected are being joined with another table in an outer select
statement. The problem is getting my inner sub-select to group by just the
year and not the entire date field. I have tried the following;

GROUP BY Product, Year_Ordered (Didn't work, Year_Ordered not a
column in table)
GROUP BY Product, 2 (Numeric positioning not
allowed for GROUP BY)
GROUP BY Product, Year(Order_Date) (Syntax)
GROUP BY Product, Order_Date (Will return multiple rows for
the same year)

Again, I would really like to avoid defining a traditional view on this
table and would also like to stay away from nesting another sub-select. We
are currently running DB2 for OS/390 Version 5.

Forrest Derrick



[login to unmask email]

Re: SQL SELECT with GROUP BY
(in response to fderrick@scfbins.com)
As all of us know that the sequence of the (hypothetical) operations is:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause

you can code ur query like this which will be an inline views query
..........................

select product,year_ordered,sum(cost_amount) as total from (
SELECT Product, Year(Order_Date) as Year_Ordered, Cost_Amount

FROM PRODUCT_ORDERS) as v1

GROUP BY Product, year_ordered


i hope this serve ur purpose

thanks

sanjeev







"[login to unmask email]" <[login to unmask email]>@RYCI.COM> on 12/14/99
10:16:06 PM

Please respond to [login to unmask email]

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: SQL SELECT with GROUP BY


In the following SQL query

SELECT Product, Year(Order_Date) as Year_Ordered, Sum(Cost_Amount)
as Total
FROM PRODUCT_ORDERS
GROUP BY Product, ? <-- After Product, I would like to group by the
Order_Date year only.

I know that an inline view could be used as well as a traditional view
definition. By the way, I am already coding this as an inline view where
the columns selected are being joined with another table in an outer select
statement. The problem is getting my inner sub-select to group by just the
year and not the entire date field. I have tried the following;

GROUP BY Product, Year_Ordered (Didn't work, Year_Ordered not a
column in table)
GROUP BY Product, 2 (Numeric positioning
not
allowed for GROUP BY)
GROUP BY Product, Year(Order_Date) (Syntax)
GROUP BY Product, Order_Date (Will return multiple rows for
the same year)

Again, I would really like to avoid defining a traditional view on this
table and would also like to stay away from nesting another sub-select. We
are currently running DB2 for OS/390 Version 5.

Forrest Derrick