SQL subqueries with counter

Question

SQL subqueries with counter

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a table like this:

++++++++++++++++++++++++++++++++++++++++
+ Nome  | Cargo      | Estado          +
+ ++++++++++++++++++++++++++++++++++++++
+ Joao  | Estagiário | RJ              +
+ Maria | Analista   | RJ              +
+ Thiago| Gerente    | SP              +
+ Pedro | Analista   | SP              +
+ Joana | Estagiário | MG              +
++++++++++++++++++++++++++++++++++++++++

And I would like to do a select in SQL to get a result like this:

+++++++++++++++++++++++++++++++++++++++++++++++++
+ Estado| Estagiário    | Analista  | Gerente   +
+ +++++++++++++++++++++++++++++++++++++++++++++++
+ MG    | 2             | 3         | 1         +
+ RJ    | 1             | 2         | 1         +
+ SP    | 1             | 2         | 3         +
+++++++++++++++++++++++++++++++++++++++++++++++++

I've used the following query:

select count distinct Cargo
from tabela1
where Cargo in ('Analista')
group by Estado

But it did not work very well.

sql group-by

asked by anonymous 24.03.2018 / 18:11

2 answers

Is it possible to call a method inside another instance? How to insert excel table in mysql using php

score 1 · Answer 1

This query should work:

SELECT ESTADO,
  COUNT(CASE WHEN CARGO = "ESTAGIARIO" THEN 1 END) estag,
  COUNT(CASE WHEN CARGO = "ANALISTA" THEN 1 END) analista,
  COUNT(CASE WHEN CARGO = "GERENTE" THEN 1 END) gerente
FROM suaTabela
GROUP BY ESTADO;

score 0 · Answer 2

You can perform a pivot.:

SELECT Estado, [Estagiário], [Analista], [Gerente]
FROM (
    SELECT 
        Estado, 
        Cargo 
    FROM @tabela
) AS t
PIVOT(
    COUNT(Cargo) 
    FOR Cargo IN ([Estagiário], [Analista], [Gerente])
) AS p