Media calculation between SQL Selects

Asked

Viewed 1,537 times

0

Good Afternoon Please help me out

Error in the Syntax
Msg 102, Level 15, State 1, Line 2087
Incorrect syntax near 'TAB'.

--TARIFAS
--Calcular Média Tarifada 


BEGIN TRY DROP TABLE #TARIFASINDMETRO END TRY BEGIN CATCH END CATCH
SELECT 

        'IND' AS industria,
         PD.[dtt_data_captura],
         AVG(PD.flt_base) AS flt_base, 
         D.[Flt Type], 
         MT.AD,
D.[dtt_data]


    INTO #TARIFASINDMETRO
    FROM #Data D
         INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                   PD.[dtt_geracao_ql2]     = D.QTS AND
                                   PD.[vch_empresa_editada] = D.[vch_empresa_editada] AND
                                   --PD.[vch_tipo_arquivo]    = D.[vch_tipo_arquivo] AND
                                   PD.[vch_od]              = D.[vch_od] AND
  PD.[dtt_data]            = D.[dtt_data] AND
                                   PD.[Flt Type]            = D.[Flt Type]

INNER JOIN @Metro MT   ON MT.OC                    = D.[vch_od]                                                                      


WHERE     D.[vch_empresa_editada] IN ('G3','JJ','OC') 


GROUP BY PD.[dtt_data_captura],
         D.[Flt Type], MT.AD, D.[dtt_data]

But by doing the same code by changing the ending: WHERE D.[vch_empresa_edited] IN ('G3','JJ','OC') ,

is bringing me the total column average and not row by row.

example 

                                IND     Média Tarifada 
804.9   669.9   NULL    NULL    592.9   737.4 
804.9   592.9   NULL    NULL    592.9   698.9 
804.9   639.9   NULL    NULL    592.9   722.4 
804.9   529.9   NULL    NULL    592.9   667.4 
804.9   419.9   NULL    NULL    592.9   612.4 
804.9   409.9   NULL    NULL    592.9   607.4

The IND column is the one that needs to be inserted and is repeating. The column of the charged average is the correct result (calculation by excel Average)

sql sql-server query
  • 3

    Could you give us the full SQL please!? because there are several open parentheses without closure, which makes it impossible to know where select ends.

  • Apparently you have several Selects within GROUP BY, without closing parentheses. What is the purpose of this query? Could give us an example of desired result. You are mounting this query?

  • It is very large. There is a way to attach?

  • No need to attach it. Simply put it there, we will edit it. because we need the whole query to know where the error is.

  • Thank you, so now I need to calculate the charged average of the three G3, JJ, OC to appear in a new column.

  • Well, let’s split up. Your error message says that you have an incorrect syntax near the word TAB, but this word is not in your query. Therefore, I assume that your query is only part of a trial or TSQL. I understand that you want to calculate charged average, however, this is not a database operation. so tell which columns of which tables you want to average. And you want the result to appear where, in which table or query?

  • And now I need a column that brings the average of the three. But by making the same code changing the ending: WHERE D.[vch_empresa_edited] IN ('P1','P2','P3') is bringing me the average of the total column and not row by row.

  • Include in your select the following field, so as to distinguish in the calculation: D.[vch_empresa_edited]

  • keep your WHERE clause as is and include within the select this field used in the Where clause. Type, "select 'IND' as industria, D.[vch_empresa_editada]"

  • is bringing me the average of the total column and not row by row. The IND column is the one I need to insert and is repeating. The column of the charged average is the correct result (calculation Average by excel) I edited the code again

Show 5 more comments

1 answer

0


industria   dtt_data_captura    flt_base    vch_empresa_editada
IND 12/1/2016   1022.15 G3
IND 12/1/2016   1093.4  G3
IND 12/1/2016   950.9   G3
IND 12/1/2016   950.9   G3
IND 12/1/2016   670 JJ
IND 12/1/2016   1733.9  G3
IND 12/1/2016   1029    JJ
IND 12/1/2016   854.9   G3
IND 12/1/2016   854.9   G3
IND 12/1/2016   670 JJ
IND 12/1/2016   854.9   G3

RESOLVIDO: 
Por exemplo, se temos os seguintes dados:

G3  JJ  OC
1   10  18  23
2   20      89
3   30  22  2
teremos as seguintes médias aritméticas, por empresa:

G3  JJ  OC
1   10  18  23
2   20      89
3   30  22  2
média   20  20  38
Se optamos por calcular qual é o valor usando a média aritmética das médias aritméticas, teremos
   (20 + 20 + 38) / 3 = 26

Mas a média aritmética correta é 
  (10 + 18 + 23 + 20 + 89 + 30 + 22 + 2) / 8 = 26,75.

Para calcular média de médias, onde o número de casos de cada média varia, sugiro o uso da média aritmética ponderada:
  ((20 * 3) + (20 * 2) + (38 * 3)) / 8 = 26,75.

  • I tested, but the column IND that should bring the average calculated between the three, still repeats. He is not doing the calculation of the line G3/JJ/OC but bringing an average rate of all files, repeating the same rate in the whole column

  • So if the average is calculated between 3, you should not have a table to calculate the average, just add the data of the 3 columns and divide by 3. Right?

  • Hi Andrew, look I’ve tried with aggregated function, with store Procedure and several selects. But it always brings the same result. The use of temporary tables within the code core is not possible.

  • I edited the last SELECT to try to make myself clear, and know if this is the possible calculation. Otherwise, I would like to know which SELECT generates this result of the final consultation.

  • I’ve edited the code so you can see the result you’re bringing me with the last modification. I believe it is bringing the average of all files and not performed the calculation of the media between the columns

  • Okay, look, you understand that you’re not posting the entire script involved in this, right? So, here’s what we’re gonna do. Put in a TXT file, all, I say all scripts involved; the Procedure (I believe this is a Procedure), the query that generates the final result and an example of data, if possible a print of this. Print to Imgur.com and TXT goes up in any fileshare.

  • Hello good morning, I managed to solve the problem. To calculate mean means, where the number of cases of each average varies, I used the weighted arithmetic mean. In time, the LY function divides the sum of the values by the number of cases, disregarding the absence of values (NULL). So it was not divided between them but the total file. Thank you very much for the help. I edited the script with the answer

  • Right. Well, I could have told you more clearly what you wanted, because it really got really hard to understand what was really the desired result.

Show 3 more comments

Browser other questions tagged sql sql-server query

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