is not a GROUP BY expression

Asked

Viewed 995 times

0

Hello . I am trying to execute a certain Query on Oracle and I am getting the following error : "is not a GROUP BY expression". Below is the list of tables and query:

BILL 

NRO_CONTA|COD_AGENCIA|NOME      |SALDO  |LIMITE|ENDERECO|DDD_FONE|
---------|-----------|----------|-------|------|--------|--------|
    30001|         30|ANA TERESA|   1000|   500|        |        |
    30002|         30|CAROLINA  |   5500|  2500|        |        |
    30003|         30|JOAQUIM   |    800|     0|        |        |

ENFORCEMENT FUND 

NRO_CONTA|DATA_APLIC         |COD_FUNDO|NRO_COTAS |VLR_APLIC|
    ---------|-------------------|---------|----------|---------|
        30007|2010-01-04 00:00:00|        1|999.430325|     1000|
        45004|2010-01-04 00:00:00|        1|749.572744|      750|
        30002|2010-01-04 00:00:00|        2|999.350422|     1000|

QUOTA FUND 

COD_FUNDO|DATA_COTA          |VALOR_COTA|
---------|-------------------|----------|
        1|2010-01-01 00:00:00|         1|
        2|2010-01-01 00:00:00|         1|
        3|2010-01-01 00:00:00|         1|

QUERY 

SELECT C.NRO_CONTA, C.NOME, C.SALDO ,
       ROUND(SUM(FA.COTAS * FA.VALOR_COTA),2) AS SALDO_APLIC
FROM CONTA C
  JOIN (
    SELECT FA.NRO_CONTA , FA.COD_FUNDO, FA.NRO_COTAS AS COTAS , DC.VALOR_COTA AS VALOR_COTA
    FROM FUNDOS_APLIC FA
    JOIN (SELECT COD_FUNDO, MAX(DATA_COTA) AS DATA_COTA , VALOR_COTA FROM FUNDOS_COTA GROUP BY COD_FUNDO) DC ON FA.COD_FUNDO = DC.COD_FUNDO 
    GROUP BY FA.COD_FUNDO, FA.NRO_CONTA 
  )FA  ON C.NRO_CONTA = FA.NRO_CONTA

Basically what I want to do is the following : Create a view that returns the data from ALL current accounts and includes the amount invested in mutual funds. Columns of the view: - account number - name of the account holder - current account balance - Total investment fund balance (fundos_aplic.nro_cotas * fundos_cota.valor_cota)

Someone could help me solve this problem ?

mysql sql database oracle pl-sql

  • In your outermost SELECT you used the SUM aggregation function, however you do not have a GROUP BY clause for this outermost SELECT, you have a GROUP BY clause but only for the innermost SELECT in one of the parts of JOIN. Try GROUP BY C.NRO_CONTA, C.NAME, C.BALANCE.

  • Hello friend. Thanks for your help. But I still have the same mistake : (

  • You may not have posted the full SELECT command because the parenting is incorrect and in addition you call both the FUNDOS_APLIC table and your FA SELECT.

  • It is complete. What would be the problem with parenting ?

  • It is unclear to which you wish to apply this GROUP BY clause.

2 answers

0

There are times not working with Oracle, but trying to help, it may be the lack of an aggregation function (SUM, MAX, MIN, etc.) in some fields that are not pointed as grouping fields (GROUP BY).

Below, I highlighted in the code some of these fields:

SELECT C.NRO_CONTA, C.NOME, C.SALDO ,
       ROUND(SUM(FA.COTAS * FA.VALOR_COTA),2) AS SALDO_APLIC
FROM CONTA C
  JOIN (
    SELECT 
        FA.NRO_CONTA, 
        FA.COD_FUNDO, 
        FA.NRO_COTAS AS COTAS ,  // SEM FUNÇÃO DE AGREGAÇÃO
        DC.VALOR_COTA AS VALOR_COTA // SEM FUNÇÃO DE AGREGAÇÃO
    FROM 
        FUNDOS_APLIC FA

        JOIN (
                SELECT 
                    COD_FUNDO, 
                    MAX(DATA_COTA) AS DATA_COTA , 
                    VALOR_COTA   // SEM FUNÇÃO DE AGREGAÇÃO
                FROM 
                    FUNDOS_COTA 
                GROUP BY 
                COD_FUNDO) DC ON FA.COD_FUNDO = DC.COD_FUNDO 
    GROUP BY FA.COD_FUNDO, FA.NRO_CONTA 
  )FA  ON C.NRO_CONTA = FA.NRO_CONTA

To get a better idea of the problem, I highlighted the code below:

            SELECT 
                COD_FUNDO, 
                MAX(DATA_COTA) AS DATA_COTA , 
                VALOR_COTA   // SEM FUNÇÃO DE AGREGAÇÃO
            FROM 
                FUNDOS_COTA 
            GROUP BY 
                COD_FUNDO

In it, you say that you want to group the records by COD_FUNDO (GROUP BY COD_FUNDO), and that in the grouping you will want the highest DATA_COTA (MAX(DATA_COTA)), but you do not say what will happen with the VALUE field. Do you want Add, Maximum Minimum, or want to group pro values ?? I think this may be the question.

A hug.

0

Re-formatting the SQL expression:

SELECT
  C.NRO_CONTA, C.NOME, C.SALDO ,
  ROUND(SUM(FA.COTAS * FA.VALOR_COTA),2) AS SALDO_APLIC
FROM CONTA C
  JOIN (
    SELECT 
      FA.NRO_CONTA,
      FA.COD_FUNDO,
      FA.NRO_COTAS AS COTAS,
      DC.VALOR_COTA AS VALOR_COTA
    FROM FUNDOS_APLIC FA
    JOIN (
      SELECT 
        COD_FUNDO,
        MAX(DATA_COTA) AS DATA_COTA,
        VALOR_COTA
      FROM FUNDOS_COTA 
      GROUP BY COD_FUNDO
    ) DC ON FA.COD_FUNDO = DC.COD_FUNDO 
    GROUP BY FA.COD_FUNDO, FA.NRO_CONTA 
  ) FA ON C.NRO_CONTA = FA.NRO_CONTA

makes it easier to see where the mistakes are. From the exterior queries to the interiors:

SELECT
  C.NRO_CONTA, C.NOME, C.SALDO ,
  ROUND(SUM(FA.COTAS * FA.VALOR_COTA),2) AS SALDO_APLIC
FROM CONTA C
(...)

here are placed three attributes and an aggregator function. It would be necessary a section "GROUP BY C.NRO_CONTA, C.NAME, C.BALANCE" at the end of the expression.

   (...)
   SELECT 
      FA.NRO_CONTA,
      FA.COD_FUNDO,
      FA.NRO_COTAS AS COTAS,
      DC.VALOR_COTA AS VALOR_COTA
    FROM FUNDOS_APLIC FA
    (...)
    ) DC ON FA.COD_FUNDO = DC.COD_FUNDO 
    GROUP BY FA.COD_FUNDO, FA.NRO_CONTA
    (...)

here we have a "GROUP BY FA.COD_FUNDO, FA.NRO_CONTA" but in the field listing there is no aggregator function.

Lastly

      (...)
      SELECT 
        COD_FUNDO,
        MAX(DATA_COTA) AS DATA_COTA,
        VALOR_COTA
      FROM FUNDOS_COTA 
      GROUP BY COD_FUNDO
      (...)

The aggregation is being done only by the field "COD_FUNDO" but in the field list there is also the field "VALOR_COTA" that should be inserted in the field list of "GROUP BY".

Browser other questions tagged mysql sql database oracle pl-sql

You are not signed in. Login or sign up in order to post.