here the question not about sum & max. Question Is " Display the values Of particular month and appropriate year where their nsick value is not in between 50 and 100. This is what i understood from the below mentioned original 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; " On Oct 20, 12:16 pm, ddf <orat...@msn.com> wrote: > On Oct 20, 2:35 am, SANDEEP REDDY <tosandeepyan...@gmail.com> wrote: > > > > > > > > > > > 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 <michaeljmo...@gmail.com> wrote: > > > > 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-Hide quoted text - > > > - Show quoted text - > > max() is not the same as sum(), no matter how you look at it, The > original question asked to compare sum totals for given periods. > > Your query doesn't answer the question, Michael's does: > > SQL> CREATE TABLE sick > 2 AS > 3 (SELECT 10 mnth, 98 yr, 44 nsick FROM DUAL > 4 UNION > 5 SELECT 4 mnth, 99 yr, 80 nsick FROM DUAL > 6 UNION > 7 SELECT 4 mnth, 99 yr, 33 nsick FROM DUAL > 8 UNION > 9 SELECT 8 mnth, 97 yr, 37 nsick FROM DUAL > 10 UNION > 11 SELECT 8 mnth, 97 yr, 43 nsick FROM DUAL) ; > > Table created. > > SQL> > SQL> SELECT mnth, yr, SUM (nsick) > 2 FROM sick > 3 GROUP BY mnth, yr > 4 HAVING SUM (nsick) < 50 OR SUM (nsick) > 100 ; > > MNTH YR SUM(NSICK) > ---------- ---------- ---------- > 4 99 113 > 10 98 44 > > SQL> > SQL> > SQL> select mnth,yr,max(nsick) > 2 from sick > 3 GROUP BY mnth,yr > 4 having max(nsick) NOT BETWEEN 50 and 100 > 5 order by mnth,yr; > > MNTH YR MAX(NSICK) > ---------- ---------- ---------- > 8 97 43 > 10 98 44 > > SQL> > > David Fitzjarrell -- 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
