SQL Count and sum

Question

I have 6 SQL codes and all search in the same table, it looks like this:

+-----+-----+-----+
|  A  |  B  |  C  |
+-----+-----+-----+
|  1  |  1  |  1  |
+-----+-----+-----+
|  2  |  1  |  5  |
+-----+-----+-----+
|  3  |  2  |  3  |
+-----+-----+-----+
|  4  |  1  |  1  |
+-----+-----+-----+
|  5  |  1  |  4  |
+-----+-----+-----+
|  6  |  1  |  5  |
+-----+-----+-----+
|  7  |  9  |  1  |
+-----+-----+-----+
|  8  |  3  |  1  |
+-----+-----+-----+

And the codes are these:

SELECT COUNT(*) as total, SUM(C) as soma FROM tabela WHERE B = 1;
SELECT COUNT(*) as total1 FROM tabela WHERE B = 1 AND C = 1;
SELECT COUNT(*) as total2 FROM tabela WHERE B = 1 AND C = 2;
SELECT COUNT(*) as total3 FROM tabela WHERE B = 1 AND C = 3;
SELECT COUNT(*) as total4 FROM tabela WHERE B = 1 AND C = 4;
SELECT COUNT(*) as total5 FROM tabela WHERE B = 1 AND C = 5;

The values returned are these:

total = 4
total1 = 2
total2 = 0
total3 = 0
total4 = 1
total5 = 2
soma = 16

Everything works perfectly, but would need everything to return in the same query, it is possible?

The intention is to count how many rows are in the table, whose column B is equal to a value, then return in "total" and sum all the values in column C and return in "sum", but need it to return the amount of times a value repeats in the search.

Answer 1

What you need is something like that:

select 
count(*) total,
sum(c) soma,
sum(case when C = 1 THEN 1 ELSE 0 END) Tot1,
sum(case when C = 2 THEN 1 ELSE 0 END) Tot2,
sum(case when C = 3 THEN 1 ELSE 0 END) Tot3
from tabela
where B = 1

The projections in the format sum(case when CONDICAO THEN 1 ELSE 0 END) are very useful for this type of scenario. In CONDITION you should put something that is rated for True or False. In this case, you’re testing C = 1, 2 or 3, but could you put C IN (1,2,3), combining even with logical operators, example C = 1 AND B = 2. Therefore, if the record being evaluated returns True, sum up to 1, otherwise add up to 0 (which makes no difference in the sum). Anyway, it is a way to count the records using the SUM and not the COUNT(*).