Wie kann man Standardwerte für SUMME() und ZAEHLUNG() angeben, wenn keine Daten in einer MySQL-Abfrage vorhanden sind, die GROUP BY verwendet?

Question

Ich verwende folgende Abfrage, die bis auf ein Problem für mich gut funktioniert

SELECT f.period                            AS month,
       SUM(p.revenue * ((100-q.rate)/100)) AS revenue,
       COUNT(DISTINCTq.label)              AS tot_stmt 
FROM files f, reports p, rates q,albums a
WHERE f.period IN ('2010-06-01','2010-05-01','2010-04-01','2010-03-01') 
  AND f.period_closed = TRUE
  AND q.period = f.period
  AND a.id = q.album_id
  AND p.file_id = f.id
  AND p.upc = a.upc
  AND p.revenue IS NOT NULL
GROUP BY month
ORDER BY month DESC;

O/P =>

Monatsumsatz gesamt_stmt

2010-06-01 10.00 2
2010-05-01 340.47 2

Ich wünsche mir ein Ergebnis wie das folgende:

Monatsumsatz gesamt_stmt

2010-06-01 10.00 2
2010-05-01 340.47 2
2010-04-01 0. 0
2010-03-01 0.00 0

Answer 1

SELECT f.period                                       AS month,
       IFNULL(SUM(p.revenue * ((100-q.rate)/100)), 0) AS revenue,
       COUNT(DISTINCT q.label)                        AS tot_stmt 
FROM files f 
     LEFT JOIN reports p ON f.id = p.file_id
     LEFT JOIN rates q ON f.period = q.period
     LEFT JOIN albums a ON q.album_id = a.id AND p.upc = a.upc
WHERE f.period IN ('2010-06-01','2010-05-01','2010-04-01','2010-03-01') 
  AND f.period_closed = TRUE
GROUP BY month
ORDER BY month DESC;

Erklärungen:

• die Bedingungen umgeschrieben, so dass sie JOIN Klauseln
• COUNT(column) gibt zurück. 0 wenn alle aggregierten Datensätze den Wert NULL in dieser Spalte
• SUM(column) gibt zurück. NULL wenn alle aggregierten Datensätze den Wert NULL in dieser Spalte
• müssen Sie auch erlauben p.revenue zu sein NULL (ließ das Kriterium)

---

Hinweis

Sie scheinen nichts von der Tabelle der Alben zu bekommen, also können Sie sie herausnehmen.

Answer 2

Sie benötigen eine Hilfstabelle mit den benötigten Informationen. Die Hilfstabelle enthält die Monate, auf die Sie sich beziehen:

Monat

2010-06-01 2010-05-01 2010-04-01 2010-03-01

Sobald Sie diese Tabelle haben, können Sie linke äußere Verknüpfung zu und verwenden Sie isnull Funktion, um die gewünschten Standardwerte einzugeben.

Etwa so:

SELECT f.period as month, 
       sum(p.revenue * ((100-q.rate)/100)) as revenue, 
       count(distinct q.label) as tot_stmt  
  FROM (files f left outer join periods pers on f.period = pers.period), reports p, rates q,albums a 
 WHERE f.period in ('2010-06-01','2010-05-01','2010-04-01','2010-03-01')  
   AND f.period_closed = true 
   AND q.period = f.period 
   AND a.id = q.album_id 
   AND p.file_id = f.id 
   AND p.upc = a.upc 
   AND p.revenue is not null 
 GROUP BY month ORDER BY month DESC