OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Need Query Help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need Query Help [message #371146]

Wed, 06 September 2000 21:26

Zion
Messages: 1
Registered: September 2000

Junior Member

Hello,

I am trying to create a single query with the following criteria:

I need to Sum hours(HourAmt) by Grouping Unit numbers (as well as EmpType,HourType)....the
tricky part is the Unit groupings....I need to group certain units together, ie Units 1-10 would
be placed in Unit#1, Units 11-20 in Unit#2, etc. Can this be done in a single query without using cursors...ie using sub-selects?

Table Columns:
Unit#
EmpType
HourType
HourAmt

I appreciate your help!

Report message to a moderator

Re: Need Query Help [message #371149 is a reply to message #371146]

Thu, 07 September 2000 07:59

Thierry Van der Auwera
Messages: 44
Registered: January 2000

Member

Hallo Zion,

You can do this in a single sql whithout any cursors.
The problem is that you have to know what your possible units are to code your sql.
"Or you have to build your sql at runtime."

Example1.
*********
SELECT unit , emptype , hourtype , SUM(houramt) houramt
FROM (SELECT DECODE(unit,1,1 , 2,1 , 3,1 , 4,1 , 5,1 , 6,1 , 7,1 , 8,1 , 9,1 ,10,1
11,2 ,12,2 ,13,2 ,14,2 ,15,2 ,16,2 ,17,2 ,18,2 ,19,2 ,20,2
21,3 ,22,3 ,23,3 ,24,3 ,25,3 ,26,3 ,27,3 ,28,3 ,29,3 ,30,3
,99) unit, emptyp , hourtype
FROM tab1)
GROUP BY unit , emptype , hourtype

Example2.
*********
SELECT 1 unit , emptype , hourtype , SUM(houramt) houramt
FROM tab1
WHERE unit BETWEEN 1 AND 10
GROUP BY emptype , hourtype
UNION ALL
SELECT 2 unit , emptype , hourtype , SUM(houramt) houramt
FROM tab1
WHERE unit BETWEEN 11 AND 20
GROUP BY emptype , hourtype
UNION ALL
SELECT 3 unit , emptype , hourtype , SUM(houramt) houramt
FROM tab1
WHERE unit BETWEEN 21 AND 30
GROUP BY emptype , hourtype
;

Example3.
*********
..... Maybe more

Hope this can help you further.

Greetings,

Thierry.

Report message to a moderator

Re: Need Query Help [message #371171 is a reply to message #371146]

Tue, 12 September 2000 15:07

Bala
Messages: 205
Registered: November 1999

Senior Member

Or you can have query like this
SELECT unit , emptype , hourtype , SUM(houramt) houramt from
(select decode(floor(unit/11), 0, 1, 1, 2, 2, 3, 3, 4, 99) unit, emptype, hourtype, houramt
from tab)
group by unit, emptype, hourtype

Bala

Report message to a moderator

Previous Topic:	Help! Cannot create rollback segment @insert data
Next Topic:	PL/SQL code

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 23 09:50:15 CDT 2023