SQL doubt with COUNT

Asked

Viewed 117 times

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.

sql

  • Friend, it would be interesting to post formatted SQL. It was very difficult to remove the formatting characters to edit an answer...

  • I’ll adjust and post again.

  • 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.

  • I’m seeing your answer now.

1 answer

5


Your query is quite wrong. You should not join a Count. When adding a Count it will perform an aggregation, and fatally return a single line. I believe the query you are looking for is the following:

 SELECT Year(entrada_acidente.data_criacao) AS ano,
       -- total de acidentes no sistema
       (SELECT Count(1) 
        FROM   entrada_acidente) AS countTotal,
       -- total de acidentes desse cliente, nessa ocorrencia
       -- dependendo do seu banco precisa apenas filtrar por occorrencia.id (ou item_despeda.entrada_id)
       (SELECT Count(1) 
        FROM   entrada_acidente
        WHERE  entrada_acidente.cliente_id = cliente.id
               AND entrada_acidente.ocorrencia_id = ocorrencia.id
               AND entrada_acidente.id = item_despesa.entrada_id) AS ct,
       -- soma dos valores dos acidentes desse cliente, nessa ocorrencia
       -- dependendo do seu banco precisa apenas filtrar por occorrencia.id
       (SELECT Sum(valor_unitario) 
        FROM   entrada_acidente
        WHERE  entrada_acidente.cliente_id = cliente.id
               AND entrada_acidente.ocorrencia_id = ocorrencia.id
               AND entrada_acidente.id = item_despesa.entrada_id) AS soma,

        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 
        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

  • This query I use in a report JasperReports, and is giving a exception here: TextColumnBuilder<Integer> Qtd = col.column("qtd ", "ct", type.integerType()); that is: Unknown column name ct in result set. And if I change to the others aliases that are within parentheses with select occurs the same.

  • Rodrigo, I changed the query yesterday and put the aliases out. They don’t make sense inside Count. If you make that mistake, it’s because the alias isn’t right, since I had to guess a little bit what you were up to. I imagine it’s simple for you now to adjust the aliases to achieve your goals. Try running the query in the direct database to see if the results are correct.

  • @Rodrigo is still having problems with aliases?

  • I haven’t been able to check.

  • Next, it worked, but I had to add a check on Count, because it was returning higher values,(for example where there were 4 records returned 6), I added the following: and entrada_acidente.id=item_despesa.entrada_id. Is it correct or I have problems in the structure of the tables?

  • 1

    @Rodrigo perfect! I myself was adding the conditions in the Stations looking for their joins, and I just missed that. It seems to me that your tables are correct: you are counting the accident in the customer, in the occurrence and in the item of expense returned in the registration in question, that is, your COUNT is being restricted by the same conditions of your joins. I think you’re right. I don’t have an opinion about your bank (and that wouldn’t even be within the context of Stack Overflow!), but it seems to me straight forward!

Show 1 more comment

Browser other questions tagged sql

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