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
