2
Hello, I have a query as below, and I have some questions regarding COUNT
, would like help to understand and solve the problem :
public String getQueryPrincipal(String queryPrincipal) {
return queryPrincipal = " select YEAR(entrada_acidente.data_criacao) as ano,"
//+ " COUNT(entrada_acidente.cliente_id ) AS ct,"
+ " cliente.id AS cliente_id,"
+ " cliente.doc_receita_federal AS cliente_doc_receita_federal,"
+ " cliente.email AS cliente_email,"
+ " cliente.nome AS cliente_nome,"
+ " cliente.tipo AS cliente_tipo,"
+ " condutor.id AS condutor_id,"
+ " condutor.codigo AS condutor_codigo,"
+ " condutor.nome AS condutor_nome,"
+ " despesa.id AS despesa_id,"
+ " despesa.nome AS despesa_nome,"
+ " despesa.sku AS despesa_sku,"
+ " despesa.categoria_id AS despesa_categoria_id,"
+ " entrada_acidente.id AS entrada_acidente_id,"
+ " entrada_acidente.attach AS entrada_acidente_attach,"
+ " entrada_acidente.data_criacao AS entrada_acidente_data_criacao,"
+ " entrada_acidente.fileName AS entrada_acidente_fileName,"
+ " entrada_acidente.forma_pagamento AS entrada_acidente_forma_pagamento,"
+ " entrada_acidente.observacao AS entrada_acidente_observacao,"
+ " entrada_acidente.status AS entrada_acidente_status,"
+ " entrada_acidente.valor_total AS entrada_acidente_valor_total,"
+ " entrada_acidente.cliente_id AS entrada_acidente_cliente_id,"
+ " entrada_acidente.ocorrencia_id AS entrada_acidente_ocorrencia_id,"
+ " entrada_acidente.vendedor_id AS entrada_acidente_vendedor_id,"
+ " entrada_acidente.valor_unitario AS entrada_acidente_valor_unitario,"
+ " item_despesa.id AS item_despesa_id," + " item_despesa.despesa_id AS item_despesa_despesa_id,"
+ " item_despesa.entrada_id AS item_despesa_entrada_id," + " ocorrencia.id AS ocorrencia_id,"
+ " ocorrencia.descricao AS ocorrencia_descricao,"
+ " ocorrencia.condicao_tempo AS ocorrencia_condicao_tempo,"
+ " ocorrencia.data_ocorrencia AS ocorrencia_data_ocorrencia,"
+ " ocorrencia.numero_vitimas AS ocorrencia_numero_vitimas,"
+ " ocorrencia.periodo_ocorrencia AS ocorrencia_periodo_ocorrencia"
+ " FROM " + " cliente cliente "
+ " INNER JOIN (select COUNT( * ) as countTotal " + "FROM entrada_acidente)AS t "
//+ " INNER JOIN (select COUNT( entrada_acidente.id ) as ct " + "FROM entrada_acidente)AS tt "
+ " INNER JOIN (select SUM( entrada_acidente.valor_unitario ) as soma " + "FROM entrada_acidente)AS ttt "
+ " INNER JOIN entrada_acidente entrada_acidente ON cliente.id = entrada_acidente.cliente_id "
+ " INNER JOIN item_despesa item_despesa ON entrada_acidente.id = item_despesa.entrada_id"
+ " INNER JOIN ocorrencia ocorrencia ON entrada_acidente.ocorrencia_id = ocorrencia.id"
+ " INNER JOIN condutor condutor ON ocorrencia.condutor_id = condutor.id"
+ " INNER JOIN despesa despesa ON item_despesa.despesa_id = despesa.id";
}
It is working correctly, but if I remove the comment from this line:(1st doubt)
//+ " COUNT(entrada_acidente.cliente_id ) AS ct,"
I receive only one record as return. I have 12 records at the moment.
This line : (second doubt)
COUNT(entrada_acidente.cliente_id ) AS ct
should give me the amount of each client(amount of records of each client) considering the restrictions made in the query?
This line: (3rd doubt)
+ " INNER JOIN (select COUNT( * ) as countTotal " + "FROM entrada_acidente)AS t "
should give me the total amount of records in the table?
These are the three issues I would like to help resolve/understand. Thank you.
Friend, it would be interesting to post formatted SQL. It was very difficult to remove the formatting characters to edit an answer...
– Loudenvier
I’ll adjust and post again.
– Rodrigo
I already answered, but I can’t guarantee that it is 100% because I don’t have its database structure, nor was the structure of the tables posted, so I had to assume some scenarios.
– Loudenvier
I’m seeing your answer now.
– Rodrigo