Untitled

sagres <- src_mysql('sagres_municipal', group='ministerio-publico', password=NULL, username = "empenhados")

query <- sql('
  SELECT e.*
  FROM Empenhos as e INNER JOIN (
    SELECT *
    FROM Propostas
    WHERE st_Proposta = 2) as p 
  ON (e.cd_UGestora = p.cd_UGestora AND 
      e.nu_Licitacao = p.nu_Licitacao AND 
      e.tp_Licitacao = p.tp_Licitacao AND 
      e.cd_Credor = p.nu_CPFCNPJ)
')

empenhos_para_perdedores <- tbl(sagres, query) %>%
  collect()

## Warning in .local(conn, statement, ...): Decimal MySQL column 18 imported
## as numeric

## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 21
## imported as numeric

## Warning in .local(conn, statement, ...): Decimal MySQL column 18 imported
## as numeric

## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 21
## imported as numeric

licitacoes_p_perdedores <- empenhos_para_perdedores %>%
  group_by(cd_UGestora, nu_Licitacao, tp_Licitacao) %>%
  summarise(n = n())

fornecedores <- empenhos_para_perdedores %>%
  group_by(cd_Credor) %>%
  summarise(n = n())