Forum Stats

  • 3,855,495 Users
  • 2,264,511 Discussions
  • 7,906,007 Comments

Discussions

group

948062
948062 Member Posts: 228
edited Sep 5, 2012 7:57AM in SQL & PL/SQL
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
Tagged:
«13

Answers

  • jeneesh
    jeneesh Member Posts: 7,168
    edited Sep 4, 2012 7:30AM
    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
    V prasad Member Posts: 619
    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
    hm Member Posts: 1,175
    edited Sep 4, 2012 7:33AM
    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
    DivasP Member Posts: 50
    edited Sep 4, 2012 7:37AM
    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
    948062 Member Posts: 228
    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
    948062 Member Posts: 228
    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
    Venkadesh Raja Member Posts: 1,058 Silver Badge
    Same mistake what you did yesterday !

    2437105
  • 948062
    948062 Member Posts: 228
    yes but my requirement is like:

    i'am not able to paste the table in correct format frm excel.
  • 948062
    948062 Member Posts: 228
    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
    Venkadesh Raja Member Posts: 1,058 Silver Badge
    edited Sep 4, 2012 8:31AM
    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.
This discussion has been closed.