margospencer@comcast.net wrote:

> Does db2 support this subquery:
>
> select b.time_ssno, sum(b.time_hours) as hours95,
> (select sum(a.time_hours)
> from p0230.pyrltime_time_v1 a
> where time_date between '2005-01-01' and '2005-03-31'
> and time_orgn = '2300'
> and time_type = 'REG'
> and a.time_ssno = b.time_ssno) as total_hours
> from p0230.pyrltime_time_v1 b
> where time_date between '2005-01-01' and '2005-03-31'
>
> and time_orgn = '2300' and
> time_sub_org ='95' and
> time_type = 'REG'
> group by time_ssno

What you have above in the SELECT-list is basically a scalar value
(time_ssno), an aggregate function (SUM) and another scalar value
(subselect). Now you only group by the first scalar value. How should DB2
know what to do with the second scalar value? You don't group by it and if
there are different values for each row in each group, there is no way to
know which value to pick. That's why you will get an error for the above
query.

> Iam trying to determine the total hours within a speific category and
> the total in all categories.

Have a look at ROLLUP here:
http://publib.boulder.ibm.com/infoce...n/r0000875.htm

I believe it does what you want to have.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena