SQL query to calculate child value total for parent

Question

I have the following query:

select
   AD.ALLOCATION_INSTR_ID AS "Allocation Id"
  ,AD.ALLOCATION_INSTR_VERSION AS "Allocation Version"
  ,SUM(AC.CHARGE_AMOUNT) AS "Allocation Commission"
  ,AD.ALLOCATION_ID AS "Sub Allocation Id"
  ,AD.ALLOCATION_VERSION AS "Sub Allocation Version"
  ,AC.CHARGE_AMOUNT AS "Allocation Commission"
  ,B.BLOCK_ID AS "Block Id"
  ,B.BLOCK_VERSION AS "Block Version"
  ,B.BLOCK_SOURCE_SYSTEM AS "Block Source System"
  ,BC.CHARGE_AMOUNT AS "Block Commission"
FROM  EQ_MO_ALLOCATION_DETAIL AS AD 
  LEFT OUTER JOIN EQ_MO_ALLOCATION_DETAIL_CHARGE AS AC 
      ON AC.ALLOCATION_DETAIL_OID = AD.ALLOCATION_DETAIL_OID 
  JOIN EQ_MO_ALLOCATION_INSTR AS AI 
      ON AI.ALLOCATION_INSTR_ID = AD.ALLOCATION_INSTR_ID
      AND AI.ALLOCATION_INSTR_VERSION = AD.ALLOCATION_INSTR_VERSION
  JOIN EQ_MO_ALLOCATION_INSTR_LINKAGE AS AL
      ON AI.ALLOCATION_INSTR_OID = AL.ALLOCATION_INSTR_OID 
  JOIN EQ_MO_BLOCK AS B
      ON B.BLOCK_ID = AL.LINK_PARENT_ID
      AND B.BLOCK_VERSION = AL.LINK_PARENT_VERSION
  LEFT OUTER JOIN EQ_MO_BLOCK_CHARGE AS BC 
      ON BC.BLOCK_OID = B.BLOCK_OID
WHERE AD.LAST_UPDATE_TIME BETWEEN '2013-01-17 00:00:00' AND '2013-01-18 00:00:00' 
AND AC.CHARGE_NAME = 'Commission' 
AND BC.CHARGE_NAME = 'COMMISSION'
AND AD.ALLOCATION_INSTR_VERSION = (SELECT MAX(AD1.ALLOCATION_INSTR_VERSION) 
                       FROM EQ_MO_ALLOCATION_DETAIL AD1
                       WHERE AD1.ALLOCATION_INSTR_VERSION = AD.ALLOCATION_INSTR_VERSION)
AND B.BLOCK_TIME = (SELECT MAX(b1.BLOCK_TIME)
                    FROM EQ_MO_BLOCK b1
                    WHERE b1.BLOCK_ID = B.BLOCK_ID
                   AND b1.BLOCK_VERSION = B.BLOCK_VERSION)
AND AI.VENDOR_PRODUCT = 'OA'
GROUP BY AD.ALLOCATION_INSTR_ID
ORDER BY 1,2

I'm trying to calculate the total of Allocation Commission via the gathering the total of the Charge amount and display that versus the parent id "Allocation id"

However my group by is some what disjointed:

I join the grouped child commissions to their parent, which is located in another table but I am joining the two via:

LEFT OUTER JOIN EQ_MO_ALLOCATION_DETAIL_CHARGE AS AC 
  ON AC.ALLOCATION_DETAIL_OID = AD.ALLOCATION_DETAIL_OID

Table AD contains the parent id which can be seen in the first select.

However I'm given the error of:

ERROR:  Attribute AD.ALLOCATION_INSTR_VERSION must be GROUPed or used in an aggregate function

From what I see, every field in the select list, except for `SUM(AC.CHARGE_AMOUNT) AS "Allocation Commission"` should appear in the `GROUP BY AD.ALLOCATION_INSTR_ID` line after a comma. If you just add AD.ALLOCATION_INSTR_VERSION, then You will probably end up with the same error for another field. — Kitet, Jan 24 '13 at 11:03
It would be easier to help with example data and required output. — DMK, Jan 24 '13 at 11:08

DMK · Answer 1 · 2013-01-24T14:14:22.503

If you GROUP BY AD.ALLOCATION_INSTR_ID then all other columns within the Select statement that are not part of the Group by clause need to be part of an aggregate function such as Max(AD.ALLOCATION_INSTR_VERSION)

If the only reason your using the Group by is to get the SUM(AC.CHARGE_AMOUNT) then you could think about doing this within a sub-query and then join it to your main Select statement.

Edit :-

Something like the following might give the desired results (Not tested):

SELECT
   AD.ALLOCATION_INSTR_ID AS "Allocation Id"
  ,AD.ALLOCATION_INSTR_VERSION AS "Allocation Version"
  ,J.Commission AS "Allocation Commission"
  ,AD.ALLOCATION_ID AS "Sub Allocation Id"
  ,AD.ALLOCATION_VERSION AS "Sub Allocation Version"
  ,AC.CHARGE_AMOUNT AS "Allocation Commission"
  ,B.BLOCK_ID AS "Block Id"
  ,B.BLOCK_VERSION AS "Block Version"
  ,B.BLOCK_SOURCE_SYSTEM AS "Block Source System"
  ,BC.CHARGE_AMOUNT AS "Block Commission"
FROM  EQ_MO_ALLOCATION_DETAIL AS AD
LEFT OUTER JOIN EQ_MO_ALLOCATION_DETAIL_CHARGE AS AC
    ON AC.ALLOCATION_DETAIL_OID = AD.ALLOCATION_DETAIL_OID
JOIN EQ_MO_ALLOCATION_INSTR AS AI
    ON AI.ALLOCATION_INSTR_ID = AD.ALLOCATION_INSTR_ID
    AND AI.ALLOCATION_INSTR_VERSION = AD.ALLOCATION_INSTR_VERSION
JOIN EQ_MO_ALLOCATION_INSTR_LINKAGE AS AL
    ON AI.ALLOCATION_INSTR_OID = AL.ALLOCATION_INSTR_OID
JOIN EQ_MO_BLOCK AS B
    ON B.BLOCK_ID = AL.LINK_PARENT_ID
    AND B.BLOCK_VERSION = AL.LINK_PARENT_VERSION
LEFT OUTER JOIN EQ_MO_BLOCK_CHARGE AS BC
    ON BC.BLOCK_OID = B.BLOCK_OID
RIGHT JOIN (SELECT
            AD.ALLOCATION_INSTR_ID AS "AllocId"
            ,SUM(AC.CHARGE_AMOUNT) AS "Commission"
        FROM  EQ_MO_ALLOCATION_DETAIL AS AD
        LEFT OUTER JOIN EQ_MO_ALLOCATION_DETAIL_CHARGE AS AC
            ON AC.ALLOCATION_DETAIL_OID = AD.ALLOCATION_DETAIL_OID
        JOIN EQ_MO_ALLOCATION_INSTR AS AI
            ON AI.ALLOCATION_INSTR_ID = AD.ALLOCATION_INSTR_ID
            AND AI.ALLOCATION_INSTR_VERSION = AD.ALLOCATION_INSTR_VERSION
        JOIN EQ_MO_ALLOCATION_INSTR_LINKAGE AS AL
            ON AI.ALLOCATION_INSTR_OID = AL.ALLOCATION_INSTR_OID
        JOIN EQ_MO_BLOCK AS B
            ON B.BLOCK_ID = AL.LINK_PARENT_ID
            AND B.BLOCK_VERSION = AL.LINK_PARENT_VERSION
        LEFT OUTER JOIN EQ_MO_BLOCK_CHARGE AS BC
            ON BC.BLOCK_OID = B.BLOCK_OID
        WHERE AD.LAST_UPDATE_TIME BETWEEN '2013-01-17 00:00:00' AND '2013-01-18 00:00:00'
        AND AC.CHARGE_NAME = 'Commission'
        AND BC.CHARGE_NAME = 'COMMISSION'
        AND AD.ALLOCATION_INSTR_VERSION = (
            SELECT MAX(AD1.ALLOCATION_INSTR_VERSION)
            FROM EQ_MO_ALLOCATION_DETAIL AD1
            WHERE AD1.ALLOCATION_INSTR_VERSION = AD.ALLOCATION_INSTR_VERSION)
        AND B.BLOCK_TIME = (
            SELECT MAX(b1.BLOCK_TIME)
            FROM EQ_MO_BLOCK b1
            WHERE b1.BLOCK_ID = B.BLOCK_ID
            AND b1.BLOCK_VERSION = B.BLOCK_VERSION)
        AND AI.VENDOR_PRODUCT = 'OA'
        GROUP BY AD.ALLOCATION_INSTR_ID) AS J
ON J.AllocId = AD.ALLOCATION_INSTR_ID
ORDER BY 1,2

Yeah I think you've hit the nail on the head, I'm trying to do too much within the one function. — Will, Jan 24 '13 at 11:21
@Will . . . You are not trying to do too much with one query. You just haven't expressed the query correctly. DMK is correct. Just do the summarization in a subquery and it should work out. — Gordon Linoff, Jan 24 '13 at 11:46

SQL query to calculate child value total for parent

1 Answers1