Include column in a select

Question

In my work, I always extract from sql, tables, in one of these tables the extracted information is contracts, date, dialing Qtde, service Qtde, Alo Qtde, and so on. I use Rstudio, with a connection via odbc to extract the base. The question is how I include a column in this select ai, and I need a column of phones. This 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 said, there is the possibility to include a column there, help me.

Answer 1

Ronaldo, it’s all right?

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 you bring the fields column1, column2 and the sum of the column3 FROM another SELECT that brings the fields column1, column2 and the sum of column3. As column 3 is summed, both in the first and the second SELECT, it is necessary to group the fields column1 and column2, in the two Selects. That’s why you got that GROUP BY thing popping up twice.

Anyway, if you want to bring the PHONE field, you first need to include the PHONE field in the second SELECT and also include it in the GROUP BY clause. Then include the Telephone 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 do 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"))