Re: SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/02/18
Message-ID: <34ec198c.12299115_at_192.86.155.100>#1/1

A copy of this was sent to wmorgan_at_nswc.navy.mil (Bill Morgan) (if that email address didn't require changing) On Wed, 18 Feb 1998 15:55:06 GMT, you wrote:

>I have a table with the following 2 columns:
> DEPARTMENT VARCHAR2(30)
> DATE_APPROVED DATE
>
>I am trying to figure out if it is possible to write a single SQL statement to
>give me a list of all distinct departments and the percentage of records for
>each department that have the DATE_APPROVED column less than 3 years old.
>I can get the total records for each department with:
>
> Select distinct department,count(*) from my_table
> group by department;
>
>I can get the total records with DATE_APPROVED less than 3 years for each
>department with:
>
> Select distinct department,count(*) from my_table
> where add_months(date_approved,36)>sysdate
> group by department;
>
>My question is, can these be combined somehow into one SQL statement that
>will give the ratio of the two numbers for each department? Please email any
>replies as well as posting to this group. Many thanks!
>
something like:

select department,

        count(*),
        sum(decode(sign(add_months(date_approved,36)-sysdate), 1, 1, 0)) "<3",
        round(sum(decode(sign(add_months(date_approved,36)-sysdate),
                        1, 1, 0))/count(*) * 100, 2 ) || '%' "Pct"

from D
group by department
/

will give you
- the department name

• the total count of records for that dept
• the total count of the records in that department where the (date_approved plus 36 months) MINUS sysdate is greater then zero. (sign is a function that returns 1 for numbers >0, 0 for 0, and -1 for numbers <0... the decode returns 1 for a POSITIVE result, 0 otherwise.... We then use SUM() to count them.
• the percentage (count of less then 3years/total count)

>---------------------------------------------------------------------------
>William E. Morgan NAVAL SURFACE WARFARE CENTER
>Code N83 email: wmorgan_at_nswc.navy.mil
>17320 Dahlgren Rd. phone: (540)653-8446,(540)653-7151
>Dahlgren, VA 22448 fax: (540)653-1810
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Feb 18 1998 - 00:00:00 CET