I have a little problem for you. I would like to sort by day, but if several with different times are sorted on the day, it separates them. How do I get it out that he only shows all day by day

My date field looks like this

Datum                         weight
2022-12-09 07:12:49.000       2150
2022-12-09 08:15:49.000       1250

My query

Select FORMAT(Datum, 'dd/MM/yyyy', 'de-DE') as Datum, SUM(weight)
FROM [Produktionsdaten].[dbo].[produktionedelstahl]
WHERE Anlage IN ('Bonak 2')
  AND YEAR(Datum)=YEAR(CURRENT_TIMESTAMP)
  AND MONTH(Datum)=MONTH(CURRENT_TIMESTAMP)
group BY (Datum)

I want the day to be summarized

Datum           weight
12-09-2022      3400

CodePudding user response：

I think you were nearly there - just change the last line

Select FORMAT(Datum, 'dd/MM/yyyy', 'de-DE') as Datum, SUM(weight)
FROM [Produktionsdaten].[dbo].[produktionedelstahl]
WHERE Anlage IN ('Bonak 2')
  AND YEAR(Datum)=YEAR(CURRENT_TIMESTAMP)
  AND MONTH(Datum)=MONTH(CURRENT_TIMESTAMP)
group BY FORMAT(Datum, 'dd/MM/yyyy', 'de-DE')

CodePudding user response：

could you try to perform the SELECT in this way.

SELECT FORMAT(DATE(Datum), 'dd/MM/yyyy', 'de-DE') AS Datum, SUM(weight)
FROM [Produktionsdaten].[dbo].[produktionedelstahl]
WHERE Anlage IN ('Bonak 2')
AND YEAR(Datum)=YEAR(CURRENT_TIMESTAMP)
AND MONTH(Datum)=MONTH(CURRENT_TIMESTAMP)
GROUP BY DATE(Datum)

CodePudding user response：

You can do it by casting the DATETIME to a DATE and going from there.

SELECT CAST(Datum AS DATE) AS Datum, SUM(Weight) AS Weight
FROM [Produktionsdaten].[dbo].[produktionedelstahl]
GROUP BY CAST(Datum AS DATE)

Here is a very small sample script to demonstrate that this works:

DECLARE @Data TABLE (Datum DATETIME, Weight INT);
INSERT INTO @Data(Datum, Weight) VALUES ('2022-12-09 07:12:49.000', 2150), ('2022-12-09 08:15:49.000', 1250);

SELECT CAST(Datum AS DATE) AS Datum, SUM(Weight) AS Weight
FROM @Data 
GROUP BY CAST(Datum AS DATE)

Output: