how to solve the error “Cannot perform an aggregate function on an expression containing an aggregate or a...

up vote
0
down vote

favorite

how to solve this error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

SELECT        tblPR.PRNO, tblPRMaterial.PRMaterialCode, ISNULL(SUM(DISTINCT tblPRMaterial.PRReqdQty - SUM(tblPOFromPR.Qty)), tblPRMaterial.PRReqdQty) AS PR_Qty

FROM            tblPR INNER JOIN

                         tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                         tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                         tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode, tblPRMaterial.PRReqdQty

ORDER BY tblPR.PRNO

asked Nov 19 at 6:34

K.Badra

1

Which dbms are you using?
– jarlh
Nov 19 at 7:38

Add some sample table data and the expected result - all as formatted text, not images. (Take a look at stackoverflow.com/help/mcve before you start.)
– jarlh
Nov 19 at 7:39

Unrelated, but: dba.stackexchange.com/questions/154251
– a_horse_with_no_name
Nov 19 at 8:33

The reason seems obvious -- you have nested SUM(). What you actually want to do is less obvious, so how to fix it is not apparent.
– Gordon Linoff
Nov 19 at 12:33

add a comment |

up vote
0
down vote

favorite

how to solve this error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

SELECT        tblPR.PRNO, tblPRMaterial.PRMaterialCode, ISNULL(SUM(DISTINCT tblPRMaterial.PRReqdQty - SUM(tblPOFromPR.Qty)), tblPRMaterial.PRReqdQty) AS PR_Qty

FROM            tblPR INNER JOIN

                         tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                         tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                         tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode, tblPRMaterial.PRReqdQty

ORDER BY tblPR.PRNO

asked Nov 19 at 6:34

K.Badra

1

Which dbms are you using?
– jarlh
Nov 19 at 7:38

Add some sample table data and the expected result - all as formatted text, not images. (Take a look at stackoverflow.com/help/mcve before you start.)
– jarlh
Nov 19 at 7:39

Unrelated, but: dba.stackexchange.com/questions/154251
– a_horse_with_no_name
Nov 19 at 8:33

The reason seems obvious -- you have nested SUM(). What you actually want to do is less obvious, so how to fix it is not apparent.
– Gordon Linoff
Nov 19 at 12:33

add a comment |

up vote
0
down vote

favorite

how to solve this error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

SELECT        tblPR.PRNO, tblPRMaterial.PRMaterialCode, ISNULL(SUM(DISTINCT tblPRMaterial.PRReqdQty - SUM(tblPOFromPR.Qty)), tblPRMaterial.PRReqdQty) AS PR_Qty

FROM            tblPR INNER JOIN

                         tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                         tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                         tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode, tblPRMaterial.PRReqdQty

ORDER BY tblPR.PRNO

asked Nov 19 at 6:34

K.Badra

how to solve this error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

SELECT        tblPR.PRNO, tblPRMaterial.PRMaterialCode, ISNULL(SUM(DISTINCT tblPRMaterial.PRReqdQty - SUM(tblPOFromPR.Qty)), tblPRMaterial.PRReqdQty) AS PR_Qty

FROM            tblPR INNER JOIN

                         tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                         tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                         tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode, tblPRMaterial.PRReqdQty

ORDER BY tblPR.PRNO

sql

asked Nov 19 at 6:34

K.Badra

asked Nov 19 at 6:34

K.Badra

asked Nov 19 at 6:34

K.Badra

asked Nov 19 at 6:34

K.Badra

asked Nov 19 at 6:34

K.Badra

1

Which dbms are you using?
– jarlh
Nov 19 at 7:38

Add some sample table data and the expected result - all as formatted text, not images. (Take a look at stackoverflow.com/help/mcve before you start.)
– jarlh
Nov 19 at 7:39

Unrelated, but: dba.stackexchange.com/questions/154251
– a_horse_with_no_name
Nov 19 at 8:33

The reason seems obvious -- you have nested SUM(). What you actually want to do is less obvious, so how to fix it is not apparent.
– Gordon Linoff
Nov 19 at 12:33

add a comment |

1

Which dbms are you using?
– jarlh
Nov 19 at 7:38

Add some sample table data and the expected result - all as formatted text, not images. (Take a look at stackoverflow.com/help/mcve before you start.)
– jarlh
Nov 19 at 7:39

Unrelated, but: dba.stackexchange.com/questions/154251
– a_horse_with_no_name
Nov 19 at 8:33

The reason seems obvious -- you have nested SUM(). What you actually want to do is less obvious, so how to fix it is not apparent.
– Gordon Linoff
Nov 19 at 12:33

Which dbms are you using?
– jarlh
Nov 19 at 7:38

Add some sample table data and the expected result - all as formatted text, not images. (Take a look at stackoverflow.com/help/mcve before you start.)
– jarlh
Nov 19 at 7:39

Unrelated, but: dba.stackexchange.com/questions/154251
– a_horse_with_no_name
Nov 19 at 8:33

The reason seems obvious -- you have nested SUM(). What you actually want to do is less obvious, so how to fix it is not apparent.
– Gordon Linoff
Nov 19 at 12:33

add a comment |

3 Answers
3

active

oldest

votes

up vote
0
down vote

You can try below - you need to add sum(tblPRMaterial.PRReqdQty) in your isnull function

    SELECT tblPR.PRNO, tblPRMaterial.PRMaterialCode, 

   coalesce(SUM(DISTINCT tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), 

   sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO

answered Nov 19 at 6:37

fa06

9,3981917

@K.Badra, does it help u?
– fa06
Nov 19 at 6:57

add a comment |

up vote
0
down vote

number of column in selection and group by must be same

    SELECT  tblPR.PRNO,

 tblPRMaterial.PRMaterialCode, 

ISNULL(SUM(tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO,tblPRMaterial.PRMaterialCode

answered Nov 19 at 6:37

Zaynul Abadin Tuhin

10.8k2731

add a comment |

up vote
0
down vote

Because Aggregate cannot contain another Aggregate expression on it.. Maybe you can use explicit to do Sum Operation..

SELECT 

  PRNO,

  PRMaterialCode,

  ISNULL(SUM(SUM_PR), PRReqdQty) AS PR_QTY

FROM

(SELECT        

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode,

  tblPRMaterial.PRReqdQty, 

  DISTINCT tblPRMaterial.PRReqdQty - SUM(tblPOFromPR.Qty) AS SUM_PR

FROM 

  tblPR 

  INNER JOIN tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO 

                           AND (tblPRMaterial.PRItemStatus = 0)

  INNER JOIN tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode 

  LEFT OUTER JOIN tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO 

                              AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

WHERE 

  (tblPR.PRStatus = 1) 

  AND (tblPR.PRType = 'PR') 

  AND (tblPR.PRNO = 56548)

GROUP BY 

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode, 

  tblPRMaterial.PRReqdQty

ORDER BY 

  tblPR.PRNO) AS Derived_Table

GROUP BY

  PRNO,

  PRMaterialCode,

  PRReqdQty

ORDER BY

  PRNO

edited Nov 19 at 6:53

answered Nov 19 at 6:48

dwir182

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53369444%2fhow-to-solve-the-error-cannot-perform-an-aggregate-function-on-an-expression-co%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
0
down vote

You can try below - you need to add sum(tblPRMaterial.PRReqdQty) in your isnull function

    SELECT tblPR.PRNO, tblPRMaterial.PRMaterialCode, 

   coalesce(SUM(DISTINCT tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), 

   sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO

answered Nov 19 at 6:37

fa06

9,3981917

@K.Badra, does it help u?
– fa06
Nov 19 at 6:57

add a comment |

up vote
0
down vote

You can try below - you need to add sum(tblPRMaterial.PRReqdQty) in your isnull function

    SELECT tblPR.PRNO, tblPRMaterial.PRMaterialCode, 

   coalesce(SUM(DISTINCT tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), 

   sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO

answered Nov 19 at 6:37

fa06

9,3981917

@K.Badra, does it help u?
– fa06
Nov 19 at 6:57

add a comment |

up vote
0
down vote

You can try below - you need to add sum(tblPRMaterial.PRReqdQty) in your isnull function

    SELECT tblPR.PRNO, tblPRMaterial.PRMaterialCode, 

   coalesce(SUM(DISTINCT tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), 

   sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO

answered Nov 19 at 6:37

fa06

9,3981917

You can try below - you need to add sum(tblPRMaterial.PRReqdQty) in your isnull function

    SELECT tblPR.PRNO, tblPRMaterial.PRMaterialCode, 

   coalesce(SUM(DISTINCT tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), 

   sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO

answered Nov 19 at 6:37

fa06

9,3981917

answered Nov 19 at 6:37

fa06

9,3981917

answered Nov 19 at 6:37

fa06

9,3981917

answered Nov 19 at 6:37

fa06

9,3981917

@K.Badra, does it help u?
– fa06
Nov 19 at 6:57

add a comment |

@K.Badra, does it help u?
– fa06
Nov 19 at 6:57

@K.Badra, does it help u?
– fa06
Nov 19 at 6:57

add a comment |

up vote
0
down vote

number of column in selection and group by must be same

    SELECT  tblPR.PRNO,

 tblPRMaterial.PRMaterialCode, 

ISNULL(SUM(tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO,tblPRMaterial.PRMaterialCode

answered Nov 19 at 6:37

Zaynul Abadin Tuhin

10.8k2731

add a comment |

up vote
0
down vote

number of column in selection and group by must be same

    SELECT  tblPR.PRNO,

 tblPRMaterial.PRMaterialCode, 

ISNULL(SUM(tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO,tblPRMaterial.PRMaterialCode

answered Nov 19 at 6:37

Zaynul Abadin Tuhin

10.8k2731

add a comment |

up vote
0
down vote

number of column in selection and group by must be same

    SELECT  tblPR.PRNO,

 tblPRMaterial.PRMaterialCode, 

ISNULL(SUM(tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO,tblPRMaterial.PRMaterialCode

answered Nov 19 at 6:37

Zaynul Abadin Tuhin

10.8k2731

number of column in selection and group by must be same

    SELECT  tblPR.PRNO,

 tblPRMaterial.PRMaterialCode, 

ISNULL(SUM(tblPRMaterial.PRReqdQty) - SUM(tblPOFromPR.Qty), sum(tblPRMaterial.PRReqdQty)) AS PR_Qty

    FROM            tblPR INNER JOIN

                             tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO INNER JOIN

                             tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode LEFT OUTER JOIN

                             tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

    WHERE        (tblPR.PRStatus = 1) AND (tblPRMaterial.PRItemStatus = 0) AND (tblPR.PRType = 'PR') AND (tblPR.PRNO = 56548)

    GROUP BY tblPR.PRNO, tblPRMaterial.PRMaterialCode

    ORDER BY tblPR.PRNO,tblPRMaterial.PRMaterialCode

answered Nov 19 at 6:37

Zaynul Abadin Tuhin

10.8k2731

answered Nov 19 at 6:37

Zaynul Abadin Tuhin

10.8k2731

answered Nov 19 at 6:37

Zaynul Abadin Tuhin

10.8k2731

answered Nov 19 at 6:37

Zaynul Abadin Tuhin

10.8k2731

add a comment |

up vote
0
down vote

Because Aggregate cannot contain another Aggregate expression on it.. Maybe you can use explicit to do Sum Operation..

SELECT 

  PRNO,

  PRMaterialCode,

  ISNULL(SUM(SUM_PR), PRReqdQty) AS PR_QTY

FROM

(SELECT        

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode,

  tblPRMaterial.PRReqdQty, 

  DISTINCT tblPRMaterial.PRReqdQty - SUM(tblPOFromPR.Qty) AS SUM_PR

FROM 

  tblPR 

  INNER JOIN tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO 

                           AND (tblPRMaterial.PRItemStatus = 0)

  INNER JOIN tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode 

  LEFT OUTER JOIN tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO 

                              AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

WHERE 

  (tblPR.PRStatus = 1) 

  AND (tblPR.PRType = 'PR') 

  AND (tblPR.PRNO = 56548)

GROUP BY 

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode, 

  tblPRMaterial.PRReqdQty

ORDER BY 

  tblPR.PRNO) AS Derived_Table

GROUP BY

  PRNO,

  PRMaterialCode,

  PRReqdQty

ORDER BY

  PRNO

edited Nov 19 at 6:53

answered Nov 19 at 6:48

dwir182

add a comment |

up vote
0
down vote

Because Aggregate cannot contain another Aggregate expression on it.. Maybe you can use explicit to do Sum Operation..

SELECT 

  PRNO,

  PRMaterialCode,

  ISNULL(SUM(SUM_PR), PRReqdQty) AS PR_QTY

FROM

(SELECT        

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode,

  tblPRMaterial.PRReqdQty, 

  DISTINCT tblPRMaterial.PRReqdQty - SUM(tblPOFromPR.Qty) AS SUM_PR

FROM 

  tblPR 

  INNER JOIN tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO 

                           AND (tblPRMaterial.PRItemStatus = 0)

  INNER JOIN tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode 

  LEFT OUTER JOIN tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO 

                              AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

WHERE 

  (tblPR.PRStatus = 1) 

  AND (tblPR.PRType = 'PR') 

  AND (tblPR.PRNO = 56548)

GROUP BY 

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode, 

  tblPRMaterial.PRReqdQty

ORDER BY 

  tblPR.PRNO) AS Derived_Table

GROUP BY

  PRNO,

  PRMaterialCode,

  PRReqdQty

ORDER BY

  PRNO

edited Nov 19 at 6:53

answered Nov 19 at 6:48

dwir182

add a comment |

up vote
0
down vote

Because Aggregate cannot contain another Aggregate expression on it.. Maybe you can use explicit to do Sum Operation..

SELECT 

  PRNO,

  PRMaterialCode,

  ISNULL(SUM(SUM_PR), PRReqdQty) AS PR_QTY

FROM

(SELECT        

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode,

  tblPRMaterial.PRReqdQty, 

  DISTINCT tblPRMaterial.PRReqdQty - SUM(tblPOFromPR.Qty) AS SUM_PR

FROM 

  tblPR 

  INNER JOIN tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO 

                           AND (tblPRMaterial.PRItemStatus = 0)

  INNER JOIN tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode 

  LEFT OUTER JOIN tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO 

                              AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

WHERE 

  (tblPR.PRStatus = 1) 

  AND (tblPR.PRType = 'PR') 

  AND (tblPR.PRNO = 56548)

GROUP BY 

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode, 

  tblPRMaterial.PRReqdQty

ORDER BY 

  tblPR.PRNO) AS Derived_Table

GROUP BY

  PRNO,

  PRMaterialCode,

  PRReqdQty

ORDER BY

  PRNO

edited Nov 19 at 6:53

answered Nov 19 at 6:48

dwir182

Because Aggregate cannot contain another Aggregate expression on it.. Maybe you can use explicit to do Sum Operation..

SELECT 

  PRNO,

  PRMaterialCode,

  ISNULL(SUM(SUM_PR), PRReqdQty) AS PR_QTY

FROM

(SELECT        

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode,

  tblPRMaterial.PRReqdQty, 

  DISTINCT tblPRMaterial.PRReqdQty - SUM(tblPOFromPR.Qty) AS SUM_PR

FROM 

  tblPR 

  INNER JOIN tblPRMaterial ON tblPR.PRNO = tblPRMaterial.PRNO 

                           AND (tblPRMaterial.PRItemStatus = 0)

  INNER JOIN tblMaterial ON tblPRMaterial.PRMaterialCode = tblMaterial.MaterialCode 

  LEFT OUTER JOIN tblPOFromPR ON tblPRMaterial.PRNO = tblPOFromPR.PRNO 

                              AND tblPRMaterial.PRMaterialCode = tblPOFromPR.MaterialCode

WHERE 

  (tblPR.PRStatus = 1) 

  AND (tblPR.PRType = 'PR') 

  AND (tblPR.PRNO = 56548)

GROUP BY 

  tblPR.PRNO, 

  tblPRMaterial.PRMaterialCode, 

  tblPRMaterial.PRReqdQty

ORDER BY 

  tblPR.PRNO) AS Derived_Table

GROUP BY

  PRNO,

  PRMaterialCode,

  PRReqdQty

ORDER BY

  PRNO

edited Nov 19 at 6:53

answered Nov 19 at 6:48

dwir182

edited Nov 19 at 6:53

answered Nov 19 at 6:48

dwir182

answered Nov 19 at 6:48

dwir182

answered Nov 19 at 6:48

dwir182

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr