My understanding is that GREATEST() and LEAST() are not part of the SQL standard, but are very common.
I'm wondering, is there a way to clone the functionality of GREATEST keeping within the SQL standard?
SELECT id, GREATEST(1,2,3,4,5,6,7) AS number FROM table
The fully query:
SELECT SUBSTR(section,1,2) AS campus,
AVG(GREATEST(maximum - enrolled, 0)) AS empty
FROM sectionrun
WHERE coursenumber = '105' AND subject = 'ENGL'
GROUP BY campus
GREATEST(1,2,3,4,5,6,7) AS number
can become
(select max(tmp) from (
select 1 tmp from dual
union all
select 2 tmp from dual
union all
select 3 tmp from dual
union all
select 4 tmp from dual
union all
select 5 tmp from dual
union all
select 6 tmp from dual
union all
select 7 tmp from dual
) ) AS number
You can use the CASE
expression:
SELECT SUBSTR(section,1,2) AS campus,
AVG(CASE WHEN maximum - enrolled > 0
THEN maximum - enrolled
ELSE 0
END) AS empty
FROM sectionrun
WHERE coursenumber = '105' AND subject = 'ENGL'
GROUP BY campus
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With