Wrong about what? Regards, Mike On Sat, Oct 22, 2011 at 1:54 AM, SANDEEP REDDY <tosandeepyan...@gmail.com>wrote:
> Here i am taking one example from table emp under scott user...Please > Go Through It... > Just Intimate again to me if i am wrong... > > SQL> set line 150 > SQL> set pagesize 60 > SQL> select * from emp; > > EMPNO ENAME JOB MGR HIREDATE SAL > COMM > DEPTNO > ---------- ---------- --------- ---------- --------- ---------- > ---------- > ---------- > 7369 SMITH CLERK 7902 17-DEC-80 > 800 > 20 > 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 > 300 > 30 > 7521 WARD SALESMAN 7698 22-FEB-81 1250 > 500 > 30 > 7566 JONES MANAGER 7839 02-APR-81 > 2975 > 20 > 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 > 1400 > 30 > 7698 BLAKE MANAGER 7839 01-MAY-81 > 2850 > 30 > 7782 CLARK MANAGER 7839 09-JUN-81 > 2450 > 10 > 7788 SCOTT ANALYST 7566 19-APR-87 > 3000 > 20 > 7839 KING PRESIDENT 17-NOV-81 > 5000 > 10 > 7844 TURNER SALESMAN 7698 08-SEP-81 > 1500 0 > 30 > 7876 ADAMS CLERK 7788 23-MAY-87 > 1100 > 20 > 7900 JAMES CLERK 7698 03-DEC-81 > 950 > 30 > 7902 FORD ANALYST 7566 03-DEC-81 > 3000 > 20 > 7934 MILLER CLERK 7782 23-JAN-82 > 1300 > 10 > > 14 rows selected. > > SQL> SELECT hiredate, SUM (sal) > 2 FROM emp > 3 GROUP BY hiredate > 4 HAVING SUM (sal) < 1000 OR SUM (sal) > 2000; > > HIREDATE > SUM(SAL) > --------- > ---------- > 17-NOV-81 > 5000 > 03-DEC-81 > 3950 > 17-DEC-80 > 800 > 09-JUN-81 > 2450 > 02-APR-81 > 2975 > 19-APR-87 > 3000 > 01-MAY-81 > 2850 > > 7 rows selected. > > SQL> spool off > > > > > > On Oct 21, 10:17 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > > Sandeep, > > I think you have missed the word "total". He wants the "total" and you > get > > that by using a SUM. > > > > For example, If I ask, what is the "total" of these numbers? > > 6 > > 3 > > 1 > > your answer will be 10. > > > > Regards, > > Mike > > > > On Fri, Oct 21, 2011 at 1:57 AM, SANDEEP REDDY < > tosandeepyan...@gmail.com>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 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-Hidequotedtext > > > - > > > > > > > - 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 > > -- > 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
