He asked for the "total", not the max.
On Tue, Oct 18, 2011 at 8:32 PM, SANDEEP REDDY <tosandeepyan...@gmail.com>wrote: > select month,year,max(nsick) from sick > GROUP BY month,year > having max(nsick) NOT BETWEEN 50 and 100 > order by month,year; > > On Oct 17, 11:14 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > > SQL> CREATE TABLE sick > > AS > > (SELECT 10 mnth, 98 yr, 44 nsick FROM DUAL > > UNION > > SELECT 4 mnth, 99 yr, 80 nsick FROM DUAL > > UNION > > SELECT 4 mnth, 99 yr, 33 nsick FROM DUAL > > UNION > > SELECT 8 mnth, 97 yr, 37 nsick FROM DUAL > > UNION > > SELECT 8 mnth, 97 yr, 43 nsick FROM DUAL) > > Table created. > > SQL> SELECT mnth, yr, SUM (nsick) > > FROM sick > > GROUP BY mnth, yr > > > > MNTH YR SUM(NSICK) > > ---------- ---------- ---------- > > 8 97 80 > > 4 99 113 > > 10 98 44 > > > > 3 rows selected. > > SQL> SELECT mnth, yr, SUM (nsick) > > FROM sick > > GROUP BY mnth, yr > > HAVING SUM (nsick) < 50 OR SUM (nsick) > 100 > > > > MNTH YR SUM(NSICK) > > ---------- ---------- ---------- > > 4 99 113 > > 10 98 44 > > > > 2 rows selected. > > SQL> DROP TABLE sick > > Table dropped. > > > > On Mon, Oct 17, 2011 at 10:02 AM, Jignesh Makwana < > makwanajigne...@gmail.com > > > > > > > > > > > > > > > > > wrote: > > > SELECT month, year, MAX(nsick) > > > FROM sick > > > GROUP BY month, year > > > having not between MIN(nsick) and MAX(nsick) > > > . > > > > > Hope this will wrok... > > > > > Regards, > > > Jignesh Makwana, > > > +919892500936 > > > > > On Mon, Oct 17, 2011 at 8:04 AM, elodie <elodie.gill...@gmail.com> > wrote: > > > > >> Hi everyone, > > > > >> I would appreciate if someone could help me fix the following sql > > >> query. > > > > >> The goal of the query is to find those years and months where a total > > >> is either less than 50 or more than 100. > > > > >> SELECT month, year, MAX(nsick) > > >> FROM sick > > >> GROUP BY month, year > > >> EXCEPT > > >> SELECT month, year, MAX(nsick) > > >> FROM sick > > >> GROUP BY month, year > > >> HAVING MAX(nsick)>50 AND MAX(nsick)<100; > > > > >> I get the following error message: > > >> EXCEPT > > >> * > > >> ERROR at line 4: > > >> ORA-00933: SQL command not properly ended > > > > >> Thanks in advance > > > > >> -- > > >> You received this message because you are subscribed to the Google > > >> Groups "Oracle PL/SQL" group. > > >> To post to this group, send email to Oracle-PLSQL@googlegroups.com > > >> To unsubscribe from this group, send email to > > >> oracle-plsql-unsubscr...@googlegroups.com > > >> For more options, visit this group at > > >>http://groups.google.com/group/Oracle-PLSQL?hl=en > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "Oracle PL/SQL" group. > > > To post to this group, send email to Oracle-PLSQL@googlegroups.com > > > To unsubscribe from this group, send email to > > > oracle-plsql-unsubscr...@googlegroups.com > > > For more options, visit this group at > > >http://groups.google.com/group/Oracle-PLSQL?hl=en > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
