Doubt in the method of using Distinct and Count together!

Question

I have a requested table 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 a few attempts and got nothing!

The most I got was the result below in Mysql Workbrench, but I could not play it in my code:

Status    | Qtd
Proposta  | 03
Venceu    | 01

Code executed in Mysql Workbrench:

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

And how to do this in Laravel? Below I’m 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'));
    }
}

Answer 1

You don’t need the distinct, the group by does all the work 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 status without repeating, omitting customers.