Home » RDBMS Server » Server Administration » Need Query Help
Need Query Help [message #371146] Wed, 06 September 2000 21:26 Go to next message
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!
Re: Need Query Help [message #371149 is a reply to message #371146] Thu, 07 September 2000 07:59 Go to previous messageGo to next message
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.
Re: Need Query Help [message #371171 is a reply to message #371146] Tue, 12 September 2000 15:07 Go to previous message
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
Previous Topic: Help! Cannot create rollback segment @insert data
Next Topic: PL/SQL code
Goto Forum:
  


Current Time: Fri Mar 29 09:51:41 CDT 2024