Select help in two columns

Question

I have the following select command:

SELECT
pa_coduni,
pa_proc_id,
sum(case when substr(pa_cmp, 1, 4) = 2017 then pa_qtdapr else 0 end) a2017,
sum(case when substr(pa_cmp, 1, 4) = 2018 then pa_qtdapr else 0 end) a2018,
sum(pa_qtdapr) total
FROM
bacabalma
where
substr(pa_cmp, 1, 4) >= 2017
group by
pa_coduni, substr(pa_cmp, 1, 4), pa_proc_id
order by
pa_coduni, pa_proc_id

It works well, but the result comes out like this:

pa_coduni | pa_proc_id | a2017 | a2018 | total
aaa       |aaaa        | 1     | 0     | 1
aaa       |aaaa        | 0     | 1     | 1

How to make the result come like this:

pa_coduni | pa_proc_id | a2017 | a2018 | total
aaa       |aaaa        | 1     | 1     | 2

Answer 1

Remove the substr(pa_cmp, 1, 4) of GROUP BY otherwise your result will be grouped by year.

---

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