Comments embedded. On Oct 21, 1:57 am, SANDEEP REDDY <[email protected]> wrote: > 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 posted query by the OP is not correct; the problem description is. > "The goal of the query is to find those years and months where a total > is either less than 50 or more than 100. > The problem specifies 'total', not max, not min, not average. As I stated the query the OP posted doesn't satisfy the problem description, and your interpretation also does not match the problem definition (you interpreted the posted query, not the stated goal). > 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; " > The problem is solved using sum(), to aggregate the values for the given months by month so a proper result set will be returned. Michael demonstrated how this is done quite nicely, and I proved that the two queries do not return the same results. > On Oct 20, 12:16 pm, ddf <[email protected]> wrote: > > > > > On Oct 20, 2:35 am, SANDEEP REDDY <[email protected]> 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 <[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-Hidequoted 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- Hide quoted text - > > - Show quoted text - Please remember that posted queries can be incorrect so it's best to go by the problem description, and if that isn't clear ask for more details. 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 [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
