Query not working 1

[Cpreston]

Hi

I have a query that no matter what I try to do I get error messages

SELECT vwOrderVolumeByDate.DateRequired, vwOrderVolumeByDate."Level 2", SUM [Total Volume] AS M3
FROM vwOrderVolumeByDate
WHERE (DateRequired >= CAST(GETDATE() AS DATE)) AND (DateRequired <= DATEADD(day, 7, CAST(GETDATE() AS DATE)))
GROUP BY DateRequired
ORDER BY DateRequired ASC


I am currently getting
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.

Fairly new to coding so what I am doing incorrect please.

Thanks

 

[Olaf Doschke]

[Total volume] needs the square brackets (double quotation marks would also do), because there is a space in the column name.
But though it looks like SUM has it's brackets this way, too, SUM still only works in the syntax SUM(expression), not SUM expression.

So to make it short, you need [pre]SUM([Total Volume]) AS M3[/pre].

One final though: An error reported near FROM may really mean after or before from, and so the SUM was one candidate, after the from you only have a table name, which, if wrong, would lead to another error of the object not existing in the database.

Bye, Olaf.

 

[Cpreston]

Hi

Ok Yes I tried your line

SELECT vwOrderVolumeByDate.DateRequired, vwOrderVolumeByDate."Level 2", SUM([Total Volume]) AS M3
FROM vwOrderVolumeByDate
WHERE (DateRequired >= CAST(GETDATE() AS DATE)) AND (DateRequired <= DATEADD(day, 7, CAST(GETDATE() AS DATE)))
GROUP BY DateRequired
ORDER BY DateRequired ASC


and get this

Msg 8120, Level 16, State 1, Line 2
Column 'vwOrderVolumeByDate.Level 2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[Cpreston]

Hi

Got it working added the 'vwOrderVolumeByDate.Level 2' into the group by

All working now

thanks

 

[Olaf Doschke]

Yes, that should also have been obvious from the start. Your list of fields has to aggregate anything not in the grouping.
You should be aware, that just solving the error by adding another column to the group by clause, you now have other - in general smaller - groups, unless you know for sure the "Level 2" value is the same within all records of the group.

Bye, Olaf.