Issue with shipments: SQL syntax-CodePudding

I am unsure why I am getting an error. Here is the code

select extract(month from SDate) as simp, sum(quantity) as simp2
from shipment
where month(sdate) between ('01' and '04')
group by simp
order by simp2

CodePudding user response：

Simple order of operation error. Group by doesn't know about the aliased columns as the engine processes the group by before the select and aliases. So one must to group by the formula, not the alias in a group by. Order by occurs after the select is complete; so the aliases are now available in the order by.

Keep in mind SQL isn't executed TOP Down... It's more inside out I'll not get into the full order but FROM, WHERE, GROUP BY... Select are the first few which apply here.

select extract(month from SDate) as simp, sum(quantity) as simp2
from shipment
where month(sdate) between ('01' and '04')
group by extract(month from SDate)
order by simp2

CodePudding user response：

I found the answer. this issue was with the syntax of the between statement. ''' select extract(month from SDate) as simp, sum(quantity) as simp2 from shipment where month(sdate) between '01' and '04' group by simp order by simp2 DESC; '''