Questions with COUNT/GROUP BY

Question

Good afternoon, I’m having trouble with the group by clause.

I have a gigantic query, I need to quantify the contracts by Id, showing the degree of impact. Then I gave a Count(Id). The problem is that SQL doesn’t let me show the impact without adding this column to the GROUP BY clause. And when I put it, it separates by grouping first by impact and then by Id, as in the example below:

Id(int) - Quantity(int) - Impact (string)

5 - 3 - High

5 - 6 - Low

3 - 2 - High

2 - 1 - Low

But in reality, I would need it here:

Id(int) - Quantity(int) - Impact (string)

5 - 9 - High

3 - 2 - High

2 - 3 - Low

The comparison I need to make is "if the ID is duplicated, then add up the quantities and consider the greater impact between the records; if it is duplicate, consider whatever is in the record" Is there any way you can already do this grouping in the query?

Or I need to do it in C# ?

PS: This table is fictitious, actually thousands of records

Thank you

Answer 1

One solution is to make two separate queries and put them together:

CREATE DATABASE example;
use example;
CREATE TABLE impact (id INT, quantidade INT, impacto VARCHAR(100));
INSERT INTO impact (id,quantidade,impacto)
VALUES (5,3,"Alto");
INSERT INTO impact (id,quantidade,impacto)
VALUES (5,6,"Baixo");
INSERT INTO impact (id,quantidade,impacto)
VALUES (3,2,"Alto");
INSERT INTO impact (id,quantidade,impacto)
VALUES (2,1,"Baixo");

SELECT id, total, impacto
FROM
    (SELECT id, impacto
    FROM example.impact) as T1
    JOIN
    (SELECT id as id2, sum(quantidade) as total
    FROM example.impact
    GROUP BY id) as T2
    ON T1.id=T2.id2
ORDER BY id, impacto
LIMIT 3

Returns:

+------+-------+---------+
| id   | total | impacto |
+------+-------+---------+
|    2 |     1 | Baixo   |
|    3 |     2 | Alto    |
|    5 |     9 | Alto    |
+------+-------+---------+

That said, I think your problem will get much easier if the variable impacto for numeric. You need to make a greatness comparison between them, but they are strings.

If impacto is a numeric variable (High=1, and Low=0), just do:

SELECT SUM(quantidade), MAX(impacto)
FROM example.impact
GROUP BY id