Place Result Even If Registration Does Not Exist

Question

Place Result Even If Registration Does Not Exist

Navigation

#1 by (0 votes)

0

I'm trying to make a query where the column with the total 0 appears in the table even if there are no records in that particular status.

For example:

SELECT
    STATUS, COALESCE(COUNT(ID), 0) AS TOTAL
FROM 
    TABELA
WHERE
    ID_CHAVE = 1
AND
    STATUS IN (1, 2, 3, 4)
GROUP BY
    STATUS

In this case you only have records with statuses 1 and 2. But I would like it to appear in the result like this:

 STATUS    TOTAL
   1         3
   2         2
   3         0
   4         0

I want 0 to appear when it does not have records with a certain status.

I will make this query in Laravel. But first I need to know how to do it in SQL, and then convert to ORM.

My current table records:

ID      ID_USER     TITULO                      STATUS
1          1        Espetacular                    1    
1          1        Mais ou menos                  1    
1          1        Muito bom. Recomendo!          3

Look up the existing records. You only have Status 1 and 3. You do not have records with other statuses, they do not exist!

With UNION I can make it easy, but I did not want it that way.

SELECT COALESCE(STATUS, 1) AS STATUS, COUNT(ID) AS TOTAL FROM TABELA WHERE ID = 1 AND STATUS = 1
UNION ALL 
SELECT COALESCE(STATUS, 2) AS STATUS, COUNT(ID) AS TOTAL FROM TABELA WHERE ID = 1 AND STATUS = 2
UNION ALL
SELECT COALESCE(STATUS, 3) AS STATUS, COUNT(ID) AS TOTAL FROM TABELA WHERE ID = 1 AND STATUS = 3
UNION ALL
SELECT COALESCE(STATUS, 4) AS STATUS, COUNT(ID) AS TOTAL FROM TABELA WHERE ID = 1 AND STATUS = 4

mysql laravel

asked by anonymous 23.03.2016 / 13:55

1 answer

Best data type to work with money? Create query with date range in doctrine 2.0

score 0 · Accepted Answer

I have resolved. I created a table to save the statuses and did a leftJoin.

$totalRating = DealerStatusRating::leftJoin('dealer_ratings', function($query) use ($idDealer){
      $query->on('dealer_ratings.id_status', '=', 'dealer_status_ratings.id')
            ->where('id_concessionaria', '=', $idDealer);
})->groupBy('dealer_status_ratings.status')
  ->orderBy('dealer_status_ratings.id')
  ->select('dealer_status_ratings.status', 'slug', DB::raw('count(dealer_ratings.id) as total'))
  ->get();