Select with columns reporting incorrect data

Question

Select with columns reporting incorrect data

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)
#4 by (0 votes)

0

Hello

I have one more problem regarding a search in the database. I want to make a SELECT that searches for information such as company code, social reason, year and values (summed). At first, the search for information without the company name is correct, but when I added the table that contains the business name gave problem, as it is coming back repeated information, taking out the company name.

Is there a way around this? because corporate codes are not in line 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 company code and values are the same but the company name is not.

Thanks in advance for your help!

The structure of the tables follows:

[Table] informessocios

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

sql query postgresql select

asked by anonymous 08.06.2018 / 15:38

4 answers

Import file (excel, text), and display in a html page Queue with insertion of a String

score 0 · Accepted Answer

Hail boys, once again I thank you for helping me. Now I bring the solution to the problem. I refined the query and used some conditions in C #. Now the program is correct. Here's the solution:

QUERY:

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

C #

/ * Scans to verify the same businesses and adds 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 to verify the same companies, for some items that repeat more than 3 times / for (int i = 0; i

13.06.2018 / 18:45