Include column in a select

Question

Include column in a select

Navigation

#1 by (1 votes)

1

In my work, I always extract from sql, tables, in one of these tables the extracted information is contracts, date, dial qtde, attendee qtde, alo qtde, and so on. I emphasize that I use Rstudio, with a connection via odbc to extract the base. The question is how do I include a column in that select ai, and I need a column of phones. Esssa is the code

sql_server_temp  <- sqlQuery(channel = conn, query =

                              paste0("SELECT x.Contrato, x.data_registro, x.cart, SUM(x.Discado) AS disc_esf, SUM(x.Atendido) AS atend_esf, SUM(x.alo) AS alo_esf, SUM(x.cpc) AS cpc_esf,  ",

                                     "SUM(x.Discado_unique) AS disc_uni, SUM(x.Atendido_unique) AS atend_uni, SUM(x.alo_unique) AS alo_uni, SUM(x.cpc_unique) AS cpc_uni  ",

                                     "FROM (SELECT Data_Registro, 'ADM' AS cart, CAST(Contrato AS VARCHAR(100)) AS Contrato,SUM(CASE WHEN Discado = 'S' THEN 1 ELSE 0 END) AS Discado,  ",

                                     "SUM(CASE WHEN Atendido = 'S' THEN 1 ELSE 0 END) AS Atendido, SUM(CASE WHEN alo = 'S' THEN 1 ELSE 0 END) AS alo,   ",

                                     "SUM(CASE WHEN cpc = 'S' THEN 1 ELSE 0 END) AS cpc, CASE WHEN SUM(CASE WHEN Discado = 'S' THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS Discado_unique,  ",

                                     "CASE WHEN SUM(CASE WHEN Atendido = 'S' THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS Atendido_unique, CASE WHEN SUM(CASE WHEN alo = 'S' THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS alo_unique,   ",

                                     "CASE WHEN SUM(CASE WHEN cpc = 'S' THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS cpc_unique FROM Vw_Acionamento  ",

                                     "WHERE tipo_discagem = 'OUTBOUND' AND Cod_Carteira IN (98) AND Data_Registro BETWEEN '",data_ini,"' AND '",data_fini,"' ",

                                     "GROUP BY Data_Registro, CAST(Contrato AS VARCHAR(100))) AS x GROUP BY x.Contrato,x.Data_Registro, x.cart"))

As already mentioned, there is the possibility of including a column there, help me.

sql r

asked by anonymous 09.06.2018 / 02:37

1 answer

VS Code is not searching the library folders (Vendor, node_modules, etc.) after updating ___ ___ erkimt Compare arrays and highlight changes ______ qstntxt ___

I have a problem that I can not solve. I have an array with a history of occurrences that has 5 positions, and I need to make the display of these histories highlighting the different items of each array. At the moment I can get the difference between two of them, but I can not figure out the best way to receive all the differences.

%pre%

In this case the history of positions 2.1 and 0 is missing.

The array structure I have is this:

%pre%

]

______ azszpr305471 ___

I do not know why, but if you want to take it back from doing so

%pre%

You can also have another way without needing the less

%pre%

___

score 1 · Accepted Answer

Ronaldo, how are you?

The structure of your query is more or less this:

<- sqlQuery(channel = conn, query = 
              paste0("SELECT x.coluna1, x.coluna2, sum(x.col3) AS coluna3 ",
                     "FROM (SELECT coluna1, coluna2, sum(col3) as coluna3 "
                     "GROUP BY coluna1, coluna2) GROUP BY x.coluna1, x.coluna2"))

That is, you are doing a SELECT where it brings the fields column1, column2 and sum of column3 FROM another SELECT that brings the fields column1, column2 and sum of column3. As you are doing a summation of column 3, in both the first and second SELECT, it is necessary to group the column1 and column2 fields in the two SELECTs. That's why you have GROUP BY popping up twice.

Anyway, if you want to bring the PHONE field, you first need to include the TELEPHONE field in the second SELECT and also include it in the GROUP BY clause. Then include the Phone in the first SELECT, but also do not forget to include it in the GROUP BY clause.

Example:

<- sqlQuery(channel = conn, query = 
              paste0("SELECT x.coluna1, x.coluna2, sum(x.col3) AS coluna3, x.TELEFONE ",
                     "FROM (SELECT coluna1, coluna2, sum(col3) as coluna3, TELEFONE "
                     "GROUP BY coluna1, coluna2, TELEFONE) GROUP BY x.coluna1, x.coluna2, x.TELEFONE"))

As I do not have your database, I could not take the test, but your query will look like this:

<- sqlQuery(channel = conn, query =

              paste0("SELECT x.Contrato, x.data_registro, x.cart, SUM(x.Discado) AS disc_esf, SUM(x.Atendido) AS atend_esf, SUM(x.alo) AS alo_esf, SUM(x.cpc) AS cpc_esf,  ",

                     "SUM(x.Discado_unique) AS disc_uni, SUM(x.Atendido_unique) AS atend_uni, SUM(x.alo_unique) AS alo_uni, SUM(x.cpc_unique) AS cpc_uni, x.TELEFONE  ",

                     "FROM (SELECT Data_Registro, 'ADM' AS cart, CAST(Contrato AS VARCHAR(100)) AS Contrato,SUM(CASE WHEN Discado = 'S' THEN 1 ELSE 0 END) AS Discado,  ",

                     "SUM(CASE WHEN Atendido = 'S' THEN 1 ELSE 0 END) AS Atendido, SUM(CASE WHEN alo = 'S' THEN 1 ELSE 0 END) AS alo,   ",

                     "SUM(CASE WHEN cpc = 'S' THEN 1 ELSE 0 END) AS cpc, CASE WHEN SUM(CASE WHEN Discado = 'S' THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS Discado_unique,  ",

                     "CASE WHEN SUM(CASE WHEN Atendido = 'S' THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS Atendido_unique, CASE WHEN SUM(CASE WHEN alo = 'S' THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS alo_unique,   ",

                     "CASE WHEN SUM(CASE WHEN cpc = 'S' THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS cpc_unique FROM Vw_Acionamento, TELEFONE  ",

                     "WHERE tipo_discagem = 'OUTBOUND' AND Cod_Carteira IN (98) AND Data_Registro BETWEEN '",data_ini,"' AND '",data_fini,"' ",

                     "GROUP BY Data_Registro, CAST(Contrato AS VARCHAR(100)), TELEFONE) AS x GROUP BY x.Contrato,x.Data_Registro, x.cart, x.TELEFONE"))