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
Asked
Active
Viewed 32 times
-5
nicael
- 18,550
- 13
- 57
- 90
-
And what is the error? – David Mar 29 '22 at 21:55
-
And *what* error ar you getting? – derpirscher Mar 29 '22 at 21:55
-
Probably something about different capitals in 'sdate' – gnight Mar 29 '22 at 22:00
-
order of operation error. Group by occurs before select. So you can't group by simp, as the engine doesn't know about it yet. You have to `group by extract(month from SDate)` Order by simp2 works because order by occurs after the select. – xQbert Mar 29 '22 at 22:34
2 Answers
1
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 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 for the order by to use.
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, HAVING, SELECT... are the first few which apply here. More on order of operation
select extract(month from SDate) as simp, sum(quantity) as simp2
from shipment
where month(sdate) between 1 and 4
group by extract(month from SDate)
order by simp2
Another thing to consider is month returns a numeric values so you don't need '01' and '04': that just causes more implicit conversions which slow things down. Just use between 1 and 4
xQbert
- 34,733
- 2
- 41
- 62
-1
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; '''