How to generate multiple columns in a query from just one column?

Question

I need a Query that returns 2 columns summed (input and output) from the "accounting" column of the "tbl_sef" table. the value of the "accounting" column shall be in the column "input" when the column "cfop" is less than 5000. And will be from the "output" column when the value of the "cfop" column is greater than or equal to 5000.

tbl_sef:

The ideal return would be:

I tried so many ways and I couldn’t!

Answer 1

Use the aggregation function SUM together with the structure CASE:

SELECT inscricao,
       SUM(CASE WHEN cfop < 5000 THEN contabil ELSE 0 END) AS entrada,
       SUM(CASE WHEN cfop >= 5000 THEN contabil ELSE 0 END) AS saida
  FROM tbl_sef
 GROUP BY inscricao

---

GROUP BY Modifiers

The GROUP BY clause Permits a WITH ROLLUP Modifier that causes Summary output to include extra Rows that represent Higher-level (that is, super-Aggregate) Summary Operations. ROLLUP Thus Enables you to Answer questions at Multiple levels of analysis with a single query. For example, ROLLUP can be used to provide support for OLAP (Online Analytical Processing) Operations.

Suppose that a Sales table has year, country, product, and Profit Columns for Recording Sales profitability:

CREATE TABLE sales(
  year    INT,
  country VARCHAR(20),
  product VARCHAR(32),
  profit  INT
);

To Summarize table Contents per year, use a simple GROUP BY like this:

SELECT year, SUM(profit) AS profit
  FROM sales
 GROUP BY year;

+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

In free translation:

The clause GROUP BY allows a modifierWITH ROLLUP which causes the summary output to include extra lines representing top-level summary operations (i.e., super-aggregated). The ROLLUP allows you to answer questions at multiple levels of analysis with a single query. For example, ROLLUP can be used to provide support for operationsOLAP (Online Analytical Processing).

Suppose a sales chart has year, country, product and profit columns to record sales profitability:

CREATE TABLE sales(
  year    INT,
  country VARCHAR(20),
  product VARCHAR(32),
  profit  INT
);

To summarize table content per year, use a GROUP BY plain as that:

SELECT year, SUM(profit) AS profit
  FROM sales
 GROUP BY year;

+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

Answer 2

You can use CASE to seprar with SUM to add:

select
    tbl_sef.incricao,
    SUM(case
        when tbl_sef.cfop < 5000 then tbl_sef.contabil
    end case) as Entrada,
    SUM(case
        when tbl_sef.cfop >= 5000 then tbl_sef.contabil
    end case) as Saida
from
    tbl_sef