Yes max(nsick) value is same as nsick value for each record bcoz we are using Group by function with the combination of month and year
TRY IT On Oct 19, 9:04 pm, Michael Moore <[email protected]> wrote: > He asked for the "total", not the max. > > On Tue, Oct 18, 2011 at 8:32 PM, SANDEEP REDDY > <[email protected]>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 <[email protected]> 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 < > > [email protected] > > > > > 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 <[email protected]> > > 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 [email protected] > > > >> To unsubscribe from this group, send email to > > > >> [email protected] > > > >> 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 [email protected] > > > > To unsubscribe from this group, send email to > > > > [email protected] > > > > 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 [email protected] > > To unsubscribe from this group, send email to > > [email protected] > > 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
