Hi,
mit folgender Abfrage:
Code
SET @J ='%2018-10-04%';
SELECT
DATE_FORMAT(T.ausgang, '%d.%m.%Y') AS Datum,
R.rechnungsnr AS ReNr,
T.po AS KdNr,
REPLACE(CAST(CAST(SUM(REPLACE(P.preis, ',', '.')) AS DECIMAL(10,2)) + IF(sum(T.entsorgung) IS NULL, cast(replace('0,00', ',', '.') AS DECIMAL(10,2)), CAST(SUM(REPLACE(T.entsorgung, ',', '.')) AS DECIMAL(10,2))) AS DECIMAL(10,2)), '.',',') AS Betrag
FROM
`pakete` AS T,
`preise` AS P,
`rechnungen` AS R
WHERE T.ausgang LIKE @J AND T.size = P.groesse AND T.po = R.kundennr AND R.rechnungsdatum LIKE @J
GROUP BY R.rechnungsnr
UNION ALL
SELECT
DATE_FORMAT(T.ausgang, '%d.%m.%Y') AS Datum,
'Summe' ReNr,
'' KdNr,
REPLACE(CAST(CAST(SUM(REPLACE(P.preis, ',', '.')) AS DECIMAL(10,2)) + IF(sum(T.entsorgung) IS NULL, cast(replace('0,00', ',', '.') AS DECIMAL(10,2)), CAST(SUM(REPLACE(T.entsorgung, ',', '.')) AS DECIMAL(10,2))) AS DECIMAL(10,2)), '.',',') AS Betrag
FROM
`pakete` AS T,
`preise` AS P
WHERE T.ausgang LIKE @J AND T.size = P.groesse
LIMIT 1000
Alles anzeigen
erhalte ich wunschgemäß die Tagessumme:
generated 2018-11-17 13:42:28 by HeidiSQL 9.4.0.5125
Wie bekomme ich nun daraus eine Monatssumme?