Doubt in the method of using Distinct and Count together!

Navigation
4

I have a table requests as the example below: 

 Id | Cliente | Status
 1  | XPTO    | Proposta
 2  | ABCD    | Proposta
 3  | XPTO    | Venceu
 4  | XPTO    | Perdeu

And I want to present a result like this: 

Cliente | Status    | Qtd
XPTO    | Proposta  | 02
XPTO    | Venceu    | 01
ABCD    | Proposta  | 01

I've made some attempts and I have not achieved anything!

The maximum I got was the result below in MySQL Workbrench, but I could not reproduce the same in my code: 

Status    | Qtd
Proposta  | 03
Venceu    | 01

MySQL code executed in Workbrench: 

SELECT distinct (status), count(cliente) as qtd_cliente 
FROM portal.pedidos 
group by status;

And how to do this in Laravel? Below I am putting the controller code: 

class RelatorioController extends Controller
{
    public function index ()
    {
        $relatorio = \App\Pedido::join('clientes', 'cliente', '=', 'nome')
        ->get();

        $cliente = \App\Cliente::lists('nome');

        return view ('relatorio.index',compact('relatorio','cliente'));
    }

    public function status()
    {

//o código abaixo é exatamente onde tenho dúvida, por isto o mesmo não está correto!!! Estas foram tentativas mal sucedidas dos meus testes.

        $status = \App\Pedido::distinct('status')           
            ->groupby('cliente')
            ->get();    

        return view ('relatorio.status', compact ('status'));
    }
}
    
asked by anonymous 04.08.2016 / 19:24

1 answer

3

You do not need distinct , group by does the whole service in this case: 

SELECT cliente, status, count(cliente) as qtd_cliente 
FROM portal.pedidos 
group by cliente, status;

If you use distinct(status) as in your example, it will pick up the statuses without repeating, omitting the clients.

    
04.08.2016 / 21:07
Duplicate Normalizer + Regex Alert only once Session