Re: SQL question

Hi Bill,

Try this one (not tested):

select department,

          sum(decode(sign(sysdate -
add_months(date_approved,36)),1,1,0)) /

          count(*) * 100
from my_table
group by department;

The trick is, that the decode of the sign-function should return 1 if the date_approved is over three years old, and otherwise 0. The sum of 1's then gives you the count you need.

Regards,

Michael Ringbo, mri_at_dde.dk

Bill Morgan 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!
>
> -------------------------------------------------------
> -------------------
> 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
Received on Thu Feb 19 1998 - 00:00:00 CET