Re: Calling all SQL Gurus ...

From: KeyStroke (Jack L. Swayze Sr.) <KeyStrk_at_Feist.Com>
Date: Sat, 12 Dec 1998 17:51:41 -0600
Message-ID: <3673018D.965C1F04@Feist.Com>

Whew! That was a tough one. First off, let me tell you this will NOT (in ANY way shape for form) be efficient code. So if you have a DBA that watches for SQL statements that do tables scans and such, you WILL be on his hit list if you run this. Doing this type of stuff is a lot, lot easier in a spreadsheet. And this sounds like the kind of thing that someone in (say) personnel may have done once in a blue moon, so I would consider doing this in MS Excel instead, and let the machine of the person who wants to get the answer be the only machine that gets bogged down with this process.

You can mail the $100 check to my residence. I'll e-mail you personally with that address after I post this.

SELECT FIRST_NAME, EMPLOYEE_CNT, PER_CENT, RUN_TOT_PERCENT FROM (SELECT V1.A_ROWNUM, V1.FIRST_NAME, V1.EMPLOYEE_CNT, V1.PER_CENT, SUM(V2.PER_CENT) RUN_TOT_PERCENT FROM
(SELECT FIRST_NAME, EMPLOYEE_CNT, (EMPLOYEE_CNT*100)/TOT_CNT PER_CENT,
ROWNUM A_ROWNUM FROM
  (SELECT FIRST_NAME, (-1*(EMPLOYEE_CNT-100000)) EMPLOYEE_CNT FROM    (SELECT 100000 - EMPLOYEE_CNT EMPLOYEE_CNT, FIRST_NAME, COUNT(*) NO_DATA FROM
    (SELECT FIRST_NAME, COUNT(*) EMPLOYEE_CNT

      FROM EMPLOYEES
      GROUP BY FIRST_NAME

    )
    GROUP BY 100000 - EMPLOYEE_CNT, FIRST_NAME    )
  ), (SELECT COUNT(*) TOT_CNT FROM EMPLOYEE)  ) V1,
(SELECT FIRST_NAME, EMPLOYEE_CNT, (EMPLOYEE_CNT*100)/TOT_CNT PER_CENT,
ROWNUM A_ROWNUM FROM
  (SELECT FIRST_NAME, (-1*(EMPLOYEE_CNT-100000)) EMPLOYEE_CNT FROM    (SELECT 100000 - EMPLOYEE_CNT EMPLOYEE_CNT, FIRST_NAME, COUNT(*) NO_DATA FROM
    (SELECT FIRST_NAME, COUNT(*) EMPLOYEE_CNT

      FROM EMPLOYEES
      GROUP BY FIRST_NAME

    )
    GROUP BY 100000 - EMPLOYEE_CNT, FIRST_NAME    )
  ), (SELECT COUNT(*) TOT_CNT FROM EMPLOYEE)  ) V2
   WHERE V1.A_ROWNUM >= V2.A_ROWNUM
   GROUP BY V1.A_ROWNUM, V1.FIRST_NAME, V1.EMPLOYEE_CNT, V1.PER_CENT )
 WHERE RUN_TOT_PERCENT <= 80
;

PMG wrote:

> I am having a little trouble coming up with a nice little SQL statement
> that does the following:
>
> group on a column,
> come up with a count of each occurence of the group,
> sort descending on the count,
> calculate the running percentage of the total count for each group,
> and return the top N groups.
>
> ex: suppose I have a table of EMPLOYEES, and I would like to select the
> first names which comprise the top 80% of all first names in the table.
>
> Is this possible to do this as a single query?
>
> TIA
>
> Pete

Received on Sat Dec 12 1998 - 17:51:41 CST