Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    7

    Unanswered: Using SUM in Select

    Am having problems using this aggregate query. It returns separate rows and not the sums as it is supposed to. What could be the problem?

    SELECT
    Z.GROUPELEMENT_ID ,
    Z.ELEMENT_ID ,
    Z.SUBELEMENT_ID ,
    Z.COMPONENT_ID ,
    W.ELEMENTS ,
    SUM(W.TIMES * W.DIM * X.MATERIALQTY * X.MATERIALQTY * Y.RATE / V.GROSSEXTERNALAREA) AS COST_PER_GIFA,
    SUM(W.TIMES * W.DIM * X.MATERIALQTY * X.MATERIALQTY * Y.RATE) AS TARGET_COST,
    SUM(W.TIMES * W.DIM * X.MATERIALQTY) AS UNIT_QTY,
    SUM(X.MATERIALQTY * Y.RATE) AS UNIT_RATE
    FROM
    PROJECTS V INNER JOIN
    ((ELEMENTSTRADES W INNER JOIN
    (TRADESMATERIALS X INNER JOIN MATERIALS Y ON X.MATERIAL = Y.MATERIAL) ON W.TRADE = X.TRADE) RIGHT OUTER JOIN ELEMENTS Z ON Z.ELEMENTS=W.ELEMENTS) ON V.PROJECT = W.PROJECT
    GROUP BY V.PROJECT, Z.GROUPELEMENT_ID,Z.ELEMENT_ID,Z.ELEMENT_ID,Z.SUBE LEMENT_ID,Z.COMPONENT_ID,W.ELEMENTS

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by hornsVIEW View Post
    Am having problems using this aggregate query. It returns separate rows and not the sums as it is supposed to. What could be the problem?
    I am curious how you came to the conclusion that the query does not return sums. Be sure that it does. However as GROUP BY clause contains V.PROJECT column, which is not present in SELECT clause, it may look like first five shown columns are duplicate. They may be - as the distinctive value of V.PROJECT column is not shown. Is this what you call "problem"?

  3. #3
    Join Date
    Jun 2011
    Posts
    7
    Hey thanks for the reply.

    Actually on refreshing my view I was able to get the sums I required however I actually need to select the V.PROJECT column and when I include it in the select statement, I get no rows whatsoever.

    What could be the cause of this?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What could be the cause of this?
    no data matched the selection criteria
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2011
    Posts
    7
    Yeah but if you follow this thread, that does not make sense. The column whose value(s) I would like displayed are actually used in getting totals for other columns via group by.

    In other words the data is there.

    It is only when I try to select or display these column value(s) that I get no rows at all.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >In other words the data is there.
    >It is only when I try to select or display these column value(s) that I get no rows at all.
    One of two possibilities exist,
    1) either you are correct
    2) or Oracle is correct.
    I bet that Oracle better reports reality than you.
    post a reproducible test case including DDL & DML shows your conclusion is correct & valid.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2011
    Posts
    7
    I got this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •