group

948062

select approved_amount from target_aggregate where status='APPROVED' group by PA_REQUEST_ID;



SELECT req_amount from target_aggregate where level_id =(select min(level_id) from target_aggregate group by PA_REQUEST_ID);




can i use group by as i shown in above sql query

jeneesh

Why cant you try?

are you getting any error?

Anyhow, when you use GROUP BY in the subquery, you should expect multiple rows as output. So, you will have to use IN instead of "="

V prasad

Use This

SELECT req_amount FROM target_aggregate
WHERE level_id =( SELECT LEVELID FROM (select PA_REQUEST_ID, min(level_id) AS "LEVELID" from target_aggregateGROUP BY GROUP BY PA_REQUEST_ID ) )

hm

First statement is wrong, because you must use all non-aggregate columns in group by clause.

Second statements may be syntactically ok, but probably it will fail at runtime, because the "select min(level)... group by pa_request_id" will give you more than one row (one for each pa_request_id).

So I think, both are wrong.

But the main question is: What do you want to achieve? Try to explain!

Edited by: hm on 04.09.2012 04:32

DivasP

the syntax of group by statement says that u have to put all the column in the group by that are being used in the select list, use this rule and check whether u are doing right or not.

Edited by: user8578271 on Sep 4, 2012 4:37 AM

948062

hm wrote:
First statement is wrong, because you must use all non-aggregate columns in group by clause.

Second statements may be syntactically ok, but probably it will fail at runtime, because the "select min(level)..." will give you more than one row (one for each pa_request_id).

So I think, both are wrong.

But the main question is: What do you want to achieve? Try to explain!
>

First statement is wrong, because you must use all non-aggregate columns in group by clause.

Second statements may be syntactically ok, but probably it will fail at runtime, because the "select min(level)..." will give you more than one row (one for each pa_request_id).

So I think, both are wrong.

But the main question is: What do you want to achieve? Try to explain! select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,
TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,
(select TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount from TARGET_AGGREGATION_ATTENDEE where status_1='APPROVED' group by parequest) as approved_amount,
TARGET_AGGREGATION_ATTENDEE.level_id,(select
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount where level_id=(select min(level_id) from target_aggregation_attendee group by parequest)as req_amount
from TARGET_ATTENDEE
TARGET_EMPLOYEE,
TARGET_PA_REQUEST,
TARGET_AGGREGATION_ATTENDEE
where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID
and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID
and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID
and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')





The table is

TARGET_AGGREGATION_ATTENDEE
columns:
PA_REQUEST_ID STATUS APPROVED_AMOUNT LEVEL_ID REQ_AMOUNT
APAC1031 CAN 0 0.61336

APAC1031 CAN 0 8.17212
APAC1031 CAN 30 0
APAC1031 CAN 30 0

APAC1033 30
APAC1033 30
APAC1033 20
APAC1033 20
APAC1053 0
APAC1053 0 45
APAC1053 0
APAC1053 0 45
APAC1059 0
APAC1059 0
APAC1059 0 34
APAC1060 30
APAC1060 30
APAC1060 0
APAC1060 30
APAC1060 0
APAC1060 0 34
APAC1061 0 56
APAC1067 EXP 0 45
APAC1067 EXP 0 89
APAC1067 EXP 0
APAC1067 EXP 0
APAC1071 0
APAC1072 0
APAC1074 0 123
APAC1089 0 11
APAC1090 0 123
APAC1092 0 11
APAC1093 0 11
APAC1094 0 11
APAC1095 0
APAC1095 0
APAC1095 0 12354
APAC1097 0 12354
APAC1097 0
APAC1097 0
APAC1099 0
APAC1099 0
APAC1099 0 12354
APAC1100 0 12354
APAC1100 0
APAC1100 0
APAC1141 70
APAC1141 0
APAC1141 60
APAC1141 30
APAC1143 0
APAC1144 CAN 0
APAC1145 0 500
APAC1156 0
APAC1156 0 34
APAC1156 0
APAC1163 CAN 23 0 0
APAC1167 0
APAC1167 30
APAC1170 0
APAC1171 0
APAC1174 CAN 0
APAC1189 0
APAC1204 0
APAC1205 0 100
APAC1207 CAN 0
APAC1208 0
APAC1212 CAN 0
APAC1213 0
APAC1215 CAN 0
APAC1218 0
APAC1218 0
APAC1218 0 123
APAC1224 0
APAC1224 0
APAC1226 CAN 0
APAC1226 CAN 0
APAC1227 0
APAC1227 0
APAC1227 0
APAC1227 0
APAC1233 0
APAC1233 0
APAC1233 0
APAC1233 0 12
APAC1233 0 123
APAC1233 0
APAC1235 CAN 0
APAC1235 CAN 0
APAC1237 0
APAC1237 0
APAC1238 CAN 2 0 0
APAC1238 CAN 2 0 0
APAC1239 0 340
APAC1244 CAN 0
APAC1244 CAN 0
APAC1245 CAN 0
APAC1245 CAN 0
APAC1247 0
APAC1247 0
APAC1247 0 256
APAC1247 0 345
APAC1263 0 123
APAC1263 0
APAC1267 CAN 0
APAC1267 CAN 0
APAC1268 0
APAC1268 0
APAC1268 0
APAC1268 0 100
APAC1268 0 123
APAC1268 0
APAC1269 0
APAC1269 0
APAC1269 0 230
APAC1269 0 230
APAC1269 0
APAC1269 0
APAC1276 0
APAC1280 0
APAC1283 0
APAC1283 0 55
APAC1283 0
APAC1285 20
APAC1285 20 0
APAC1285 20 0
APAC1285 20 0
APAC1285 20
APAC1285 20
APAC1285 20
APAC1285 20
APAC1285 20
APAC1285 0
APAC1285 0
APAC1285 0
APAC1285 0
APAC1285 0
APAC1285 0
APAC1285 0 55
APAC1285 0 66
APAC1285 0 66
APAC1293 CAN 0
APAC1295 CAN 0
APAC1296 0
APAC1297 CAN 25 0 0
APAC1298 0 25
APAC1303 0
APAC1303 0 55
APAC1303 0 55
APAC1303 0
APAC1304 0
APAC1304 0
APAC1304 0 44
APAC1304 0 44
APAC1311 0 10
APAC1311 0
APAC1311 0
APAC1311 0 12
APAC1312 0 12
APAC1312 0 12
APAC1312 0
APAC1312 0
APAC1345 CAN 33 0 0
APAC1347 CAN 0
APAC1352 0
APAC1352 30
APAC1353 0
APAC1354 0
APAC1356 0 816.9795
APAC1356 30 0
APAC1371 0
APAC1372 0
APAC1381 0
APAC1383 0
APAC1386 CAN 0
APAC1387 0
APAC1387 0 3.6
APAC1389 30
APAC1389 0
APAC1391 0
APAC1392 0 102.204
APAC1393 0 10.206
APAC1394 0 10.206
APAC1396 0 3.6
APAC1398 CAN 0
APAC1399 0
APAC1399 0
APAC1399 0 41.814
APAC1399 0 41.814
APAC1399 0
APAC1399 0
APAC1402 0
APAC1403 0 9.828
APAC1405 30
APAC1405 0
APAC1407 0 8.208
APAC1410 0 102.15
APAC1428 Approved 0
APAC1433 0
APAC1433 0 0.036
APAC1433 0
APAC1440 CAN 0.612 0 0
APAC1445 0 58.212
APAC1445 0
APAC1447 Rejected 0
APAC1449 0 0
APAC1449 0
APAC1449 0
APAC1452 0 0.81
APAC1452 0
APAC1452 0
APAC1462 0 0
APAC1462 0
APAC1462 0
APAC1463 0 0.162
APAC1463 0 0.162
APAC1463 0
APAC1463 0
APAC1469 0 8.208
APAC1470 30
APAC1470 0
APAC1471 0
APAC1471 30
APAC1481 CAN 0.414 0 0
APAC1484 0
APAC1484 0
APAC1486 0
APAC1486 0
APAC1486 0
APAC1486 0 0.018
APAC1486 0 0.018
APAC1486 0 0.036
APAC1486 0
APAC1486 0
APAC1487 0
APAC1488 0
APAC1488 0 4.212
APAC1488 30
APAC1488 30 0
APAC1489 0
APAC1489 30 0
APAC1489 30
APAC1489 0 2.214
APAC1501 0
APAC1502 0
APAC1507 CAN 0
APAC1508 0
APAC1531 0 0.162
APAC1531 0
APAC1531 0
APAC1531 30
APAC1531 30
APAC1534 0 5.4
APAC1534 0
APAC1538 0
APAC1540 0
APAC1561 CAN 0
APAC1564 Approved 0
APAC1569 0 0
APAC1569 30 0
APAC1570 0 4.49196
APAC1570 20
APAC1570 0
APAC1573 20
APAC1573 0 4.49196
APAC1573 0
APAC1573 Approved 50
APAC1573 30
APAC1574 0
APAC1574 0
APAC1574 0
APAC1574 0
APAC1591 0 5.39396
APAC1591 0 5.39396
APAC1592 0 5.39396
APAC1593 0
APAC1595 0 5.39396
APAC1596 0 5.39396
APAC1597 0
APAC1598 0 252.56
APAC1601 0
APAC1613 CAN 0.1804 0 0
APAC1618 0
APAC1618 0
APAC1618 0
APAC1618 0
APAC1618 0
APAC1619 0
APAC1619 0
APAC1619 0
APAC1619 0
APAC1628 0
APAC1628 0
APAC1629 0 0.10824
APAC1629 0
APAC1630 0
APAC1632 0
APAC1636 0 0.01804
APAC1637 0 783.7478
APAC1637 0
APAC1637 0 783.7478
APAC1637 0
APAC1638 0
APAC1639 0
APAC1640 0
APAC1640 0
APAC1641 0
APAC1641 0 0.16236
APAC1641 0
APAC1646 0
APAC1649 CAN 0
APAC1651 0
APAC1651 20
APAC1654 0
APAC1655 0 250.756
APAC1658 CAN 61.76896 0 0
APAC1659 0
APAC1659 80
APAC1662 CAN 7.79328 0 0
APAC1662 CAN 7.63092 0 0
APAC1670 CAN 7.82936 0 0
APAC1672 0 8.22023E+16
APAC1676 0 252.56
APAC1681 0 0.1804
APAC1681 0 0.0902
APAC1681 0 0.0902
APAC1684 Approved 50
APAC1684 30
APAC1684 30
APAC1684 20
APAC1684 20
APAC1684 0
APAC1684 0
APAC1684 Approved 50
APAC1688 0 1803999.982
APAC1689 0
APAC1689 0 8.19016
APAC1694 0
APAC1694 0
APAC1695 0
APAC1695 0
APAC1695 0
APAC1695 0 0.61336
APAC1695 0
APAC1695 0
APAC1695 0
APAC1695 0 0.61336
APAC1695 0 0.61336
APAC1696 0
APAC1696 0
APAC1696 0
APAC1696 0
APAC1696 0 0.03608
APAC1696 0
APAC1696 0 0.03608
APAC1696 0 0.03608
APAC1696 0
APAC1709 0 0.03608
APAC1709 0
APAC1709 0
APAC1711 0
APAC1711 0 0.03608
APAC1711 0
APAC1712 0
APAC1714 CAN 0
APAC1717 0 0.21648
APAC1717 0
APAC1717 0
APAC1717 0
APAC1717 0
APAC1717 0 0.21648
APAC1723 0
APAC1723 0
APAC1726 0
APAC1728 Approved 2.1648 0 0
APAC1729 Approved 2.1648 0 0
APAC1730 CAN 70
APAC1730 CAN 80
APAC1730 CAN 60
APAC1730 CAN 30
APAC1730 CAN 0
APAC1731 20
APAC1731 0
APAC1734 0
APAC1734 0
APAC1734 0 384.63084
APAC1736 0
APAC1736 0
APAC1736 0 0
APAC1737 0
APAC1739 0 36.08
APAC1739 0
APAC1739 0
APAC1739 0 54.12
APAC1742 0
APAC1743 0 22.26136
APAC1746 0 270.6
APAC1748 0 0.07216
APAC1748 0
APAC1748 0
APAC1748 0 0.05412
APAC1749 0
APAC1749 0
APAC1752 0
APAC1753 CAN 90.2 0 0
APAC1754 Approved 0 30 0
APAC1754 0 617.76176
APAC1754 0
APAC1754 20 0
APAC1755 0
APAC1755 0 1.01024
APAC1756 0
APAC1756 0
APAC1756 0
APAC1756 0
APAC1758 0 623.57064
APAC1758 20 0
APAC1758 0
APAC1758 Approved 0 30 0

I need requested amount column data from target aggregate table whose level id is minimum
that corresponding value for particular request id.

and approvel amount column data from target Aggregate table such that status should Approved for that particular requst id.

948062

can u suggest for first query how to get group by request id and status=approved
for Approved amount column from table TARGET_AGGREGATION_ATTENDEE

i use this one:
select approved_amount from target_aggregate where status='APPROVED' group by PA_REQUEST_ID;

Venkadesh Raja

Same mistake what you did yesterday !

2437105

948062

yes but my requirement is like:

i'am not able to paste the table in correct format frm excel.

948062

i will give u clear details

my table is
TARGET_AGGREGATION_ATTENDEE

query i used is:

select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
TARGET_ATTENDEE.COUNTRY as atcountry,
TARGET_AGGREGATION_ATTENDEE.REQUEST_TYPE as requesttype,
TARGET_PA_REQUEST.PA_REQUEST_ID as parequest,
TARGET_PA_REQUEST.EVENT_DATE as eventdate,
TARGET_PA_REQUEST.EVENT_DESCRIPTION as eventdesc,
TARGET_PA_REQUEST.CREATED_DATE as requestdate,
TARGET_PA_REQUEST.STATUS as status,
TARGET_AGGREGATION_ATTENDEE.status,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_approved_amount as approved_amount,
TARGET_AGGREGATION_ATTENDEE.level_id,
TARGET_AGGREGATION_ATTENDEE.be_aggregation_pend_amount as req_amount
from TARGET_ATTENDEE,
TARGET_EMPLOYEE,
TARGET_PA_REQUEST,
TARGET_AGGREGATION_ATTENDEE
where TARGET_ATTENDEE.ATTENDEE_ID=TARGET_AGGREGATION_ATTENDEE.ATTENDEE_ID
and TARGET_EMPLOYEE.GE_ID=TARGET_AGGREGATION_ATTENDEE.REQUESTOR_ID
and TARGET_AGGREGATION_ATTENDEE.PA_REQUEST_ID=TARGET_PA_REQUEST.PA_REQUEST_ID
and to_char(TARGET_AGGREGATION_ATTENDEE.CREATED_DATE,'YYYY')=to_char(sysdate,'YYYY')
and TARGET_AGGREGATION_ATTENDEE.pa_request_id='NAM1421';

for one particular request id is: 'NAM1421';

i got output from my query is:
(in rows i got this output unable to separate it so making it in a column

PAREQUEST
NAM1421
NAM1421
NAM1421

STATUS_1
NULL
Approved
NULL

APPROVED_AMOUNT
Null
3
Null

LEVEL_ID
15
70
20




REQ_AMOUNT
3
0
3




i need output like:(in a single row here i'am showing in column)

PAREQUEST
NAM1421


APPROVED_AMOUNT
3


STATUS_1
Approved


LEVEL_ID
15



REQ_AMOUNT
3



the logic behind is
requested amount should print the value whose levelid is minimum for that enter request id. that corresponding value.

Approved Amount status should be approved tht data should print for that enter requestid.

Venkadesh Raja

i'm not sure.

try this !

WITH t AS 
(
select
target_employee.first_name||' ' ||target_employee.last_name as requestername, 
target_employee.ge_id as requestergeid, 
target_attendee.first_name||' ' ||target_attendee.last_name as attendeename, 
target_attendee.attendee_type_flag as attendeetyflg, 
target_attendee.us_go_attendee_flag as usgoflg, 
target_attendee.comp_govt_agency_dept as atcomp, 
target_attendee.country as atcountry, 
target_aggregation_attendee.request_type as requesttype, 
target_pa_request.pa_request_id as parequest, 
target_pa_request.event_date as eventdate, 
target_pa_request.event_description as eventdesc, 
target_pa_request.created_date as requestdate, 
target_pa_request.status as status, 
target_aggregation_attendee.status,
target_aggregation_attendee.be_aggregation_approved_amount as approved_amount, 
target_aggregation_attendee.level_id,
target_aggregation_attendee.be_aggregation_pend_amount as req_amount 
from target_attendee, 
target_employee, 
target_pa_request, 
target_aggregation_attendee 
where target_attendee.attendee_id=target_aggregation_attendee.attendee_id
and target_employee.ge_id=target_aggregation_attendee.requestor_id
and target_aggregation_attendee.pa_request_id=target_pa_request.pa_request_id
and to_char(target_aggregation_attendee.created_date,'yyyy')=to_char(sysdate,'yyyy')
and target_aggregation_attendee.pa_request_id='nam1421'
)
SELECT PAREQUEST,STATUS_1,SUM(approved_amount) approved_amount,LEVEL_ID,AVG(req_amount) req_amount
FROM t
GROUP BY PAREQUEST,LEVEL_ID,STATUS_1;

i know this code does not produce expected results..But i think GROUP BY will help you.