SEO

#1 (**permalink**) 02-27-2008, 03:30 AM

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

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

I have seen this done in SQL Server.

Thanks for any help, Margo

#2 (**permalink**) 02-27-2008, 03:30 AM

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

#3 (**permalink**) 02-27-2008, 03:30 AM

I think db2 support your query.
Please see the following query which has about same structure as yours.
SELECT b.workdept, SUM(b.salary) salary_F
, (SELECT SUM(a.salary)
FROM Employee a
WHERE edlevel between 15 and 18
AND a.workdept = b.workdept
) AS salary_total
FROM Employee b
WHERE edlevel between 15 and 18
AND b.sex = 'F'
GROUP BY workdept;
------------------------------------------------------------------------------

WORKDEPT SALARY_F SALARY_TOTAL

-------- ---------------------------------
---------------------------------
A00 52750.00
52750.00
C01 52220.00
52220.00
D11 73430.00
222100.00
D21 80800.00
128740.00
E11 56000.00
56000.00

5 record(s) selected.

#4 (**permalink**) 02-27-2008, 03:30 AM

Tonkuma wrote:

> I think db2 support your query.
> Please see the following query which has about same structure as yours.
> SELECT b.workdept, SUM(b.salary) salary_F
> , (SELECT SUM(a.salary)
> FROM Employee a
> WHERE edlevel between 15 and 18
> AND a.workdept = b.workdept
> ) AS salary_total
> FROM Employee b
> WHERE edlevel between 15 and 18
> AND b.sex = 'F'
> GROUP BY workdept;

I big difference might be that the OP had a correlated subquery...

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

#5 (**permalink**) 02-27-2008, 03:30 AM

Another way to do it may be:

select time_ssno, hours95m total_hours
from
(select time_ssno, time_sub_org, hours_sub as hours95,
sum(hours_sub) over(partition by time_ssno) as total_hours
from
(select time_ssno, time_sub_org, sum(time_hours) as hours_sub,
from p0230.pyrltime_time_v1 n
where time_date between '2005-01-01' and '2005-03-31'
and time_orgn = '2300'
and time_type = 'REG'
group by time_ssno, time_sub_org) AS T1
) AS T2
where time_sub_org ='95'

or even (seems to be too simple to be true.....)

select time_ssno,
sum(case when time_sub_org ='95'
then time_hours else 0 end) as hours95,
sum(time_hours) as total_hours
from p0230.pyrltime_time_v1 n
where time_date between '2005-01-01' and '2005-03-31'
and time_orgn = '2300'
and time_type = 'REG'
group by time_ssno

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

#6 (**permalink**) 02-27-2008, 03:30 AM

The case statment worked just fine. Thanks you all for your help.

Margo

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Subquery returning a column value