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 <[email protected]> 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
> <[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 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 <[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
>
> > --
> > 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
