Select with columns stating incorrect data

Question

Hello

I’m having another problem with a database search. I want to make a SELECT that looks for information such as Cod.empresa, razaosocial, ano e valores(somados). At first, the search for information without the social reason is correct, but when I added the table that contains the social reason, it was a problem, because repeated information is coming, excluding the social reason.

Is there any way around this? because company codes do not agree with social reasons.

below is the query:

SELECT
    i.empresa,
    j.razaosocial,
    i.exercicio,
    (
        CASE
        WHEN c.lucrocontabil = 0 THEN
            c.lucrosimples
        WHEN c.lucrosimples = 0 THEN
            c.lucrocontabil
        END
    ) AS lucro
FROM
    empresas j,
    informessocios i
LEFT JOIN (
    SELECT
        sum(COALESCE(lucrocontabil, 0)) AS lucrocontabil,
        sum(COALESCE(lucrosimples, 0)) AS lucrosimples,
        empresa,
        exercicio
    FROM
        informessocios
    WHERE
        exercicio = 2017
    AND (
        lucrocontabil > 0
        OR lucrosimples > 0
    )
    GROUP BY
        empresa,
        exercicio,
        lucrocontabil,
        lucrosimples
) c ON c.empresa = i.empresa
AND c.exercicio = i.exercicio
WHERE
    c.exercicio = 2017
GROUP BY
    i.empresa,
    i.exercicio,
    lucro,
    j.razaosocial
ORDER BY
    i.empresa

Note that the code of companies and values are equal but the social reason is not.

Thanks for your help!

Follows the structure of the tables:

[Table] information centers

CREATE TABLE public.informessocios(

empresa VarChar(6) With Comp NOT NULL 3 [] True
exercicio Long NOT NULL 3 [] True
lucrocontabil Double NULL 3 [] False
lucrosimples Double NULL 3 [] False
CONSTRAINT pk_informessocios PRIMARY KEY (empresa, exercicio, socio)
)

[Table] companies

CREATE TABLE public.empresas(

empresa VarChar(6) With Comp NOT NULL 3 [] True
razaosocial VarChar(50) With Comp NULL 3 [] False
CONSTRAINT pk_empresas PRIMARY KEY (empresa, exercicio)
)

Answer 1

Maybe the problem is in this query snippet:

FROM
    empresas j,
    informessocios i

It seems that you are mixed explicit joins with implicit ones. Also never use fields like "name" or "code" as joins keys the correct is to use a single ID.

Otherwise it is difficult to give a solution without knowing the modeling of your base, how these tables relate or even if you have duplicate data even.

Answer 2

Try the code below:

SELECT
    i.empresa,
    (SELECT top 1 j1.razaosocial from empresas j1 where j.id= j1.id) as j.razaosocial,
    i.exercicio,
    (
        CASE
        WHEN c.lucrocontabil = 0 THEN
            c.lucrosimples
        WHEN c.lucrosimples = 0 THEN
            c.lucrocontabil
        END
    ) AS lucro
FROM
    empresas j,
    informessocios i
LEFT JOIN (
    SELECT
        sum(COALESCE(lucrocontabil, 0)) AS lucrocontabil,
        sum(COALESCE(lucrosimples, 0)) AS lucrosimples,
        empresa,
        exercicio
    FROM
        informessocios
    WHERE
        exercicio = 2017
    AND (
        lucrocontabil > 0
        OR lucrosimples > 0
    )
    GROUP BY
        empresa,
        exercicio,
        lucrocontabil,
        lucrosimples
) c ON c.empresa = i.empresa
AND c.exercicio = i.exercicio
WHERE
    c.exercicio = 2017
GROUP BY
    i.empresa,
    i.exercicio,
    lucro,
    j.razaosocial
ORDER BY
    i.empresa

The idea of (SELECT top 1 j1.razaosocial from empresas j1 where j.empresa = j1.empresa) is that you have only the first name returned.

Answer 3

Save the boys, once again I thank you for your help. Now I bring the solution to the problem. I redid the query and used some conditions in C#. Now the program is correct. Follow the solution:

QUERY:

SELECT e. enterprise, e. razaosocial, i.exercicio, ( CASE WHEN I.lucrocontabil=0 THEN i.lucrosimples WHEN i.lucrosimples=0 THEN i.lucrocontabil end ) the profit FROM companies and JOIN informessocios i ON e.empresa=i.empresa AND e.exercicio=2017 WHERE i.exercicio = 2017 AND (i.lucrocontabil > 0 OR i.lucrosimples > 0) GROUP BY and.company, e. razaosocial, i.exercicio, i.lucrocontabil, i.lucrosimples ORDER BY e. company

C#

/* Scan for equal companies and sum the values*/ decimal value = 0;

            for (int i = 0; i < RelatorioContabilDataGridView.RowCount; i++)
            {
                for (int j = 1; j < RelatorioContabilDataGridView.RowCount; j++)
                {
                    if (RelatorioContabilDataGridView.Rows[i].Cells[0].Value.ToString() == RelatorioContabilDataGridView.Rows[j].Cells[0].Value.ToString() && i != j && j != i)
                    {                            
                        valor = decimal.Parse(RelatorioContabilDataGridView.Rows[i].Cells[2].Value.ToString()) + decimal.Parse(RelatorioContabilDataGridView.Rows[j].Cells[2].Value.ToString());
                        RelatorioContabilDataGridView.Rows[i].Cells[2].Value = valor;
                        RelatorioContabilDataGridView.Rows.RemoveAt(j);
                    }

                }
            }

/Make a 2nd Scan for verification of similar companies, because there are some items that repeat for more than 3 times/ for (int i = 0; i < Reportaccountingdatagridview.Rowcount; i++) { for (int j = 1; j < Reportaccountingdatagridview.Rowcount; j++) { if (Reportaccountingdatagridview.Rows[i].Cells[0].Value.Tostring() == Reportaccountingdatagridview.Rows[j].Cells[0].Value.Tostring() && i != j && j != i) { value = decimal.Parse(Reportaccountingdatagridview.Rows[i].Cells[2].Value.Tostring()) + decimal.Parse(Reportaccountingdatagridview.Rows[j].Cells[2].Value.Tostring()); Reportaryaccountingdatagridview.Rows[i]. Cells[2]. Value = value; RelatorioContabilDataGridView.Rows.RemoveAt(j); }
} }

Answer 4

Andrew, There is a command in sql named row_number, it makes a list according to the parameters.

Access the link below and see the example. http://www.postgresqltutorial.com/postgresql-row_number/

I don’t know how your base is, I think because you don’t have a centralization of data, you’re having a little trouble. I think it would be nice, you make a data normalization plan.

At first it would use the command below

ROW_NUMBER() OVER ( PARTITION BY "name_Empresa" ORDER BY "data_de_Cadastro_more_old")

A strong abs