## Question:

I have 6 SQL codes and they all search 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 returned values are these:

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

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

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

## Answer:

What you need is something like this:

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

Projections in the format `sum(case when CONDICAO THEN 1 ELSE 0 END)`

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