Re: Help with 2 queries / Join problem

On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>
>Any ideas?

Hi Dwight,

Yes, I think so. But you'll have to provide more info first:

• What RDBMS is this for? I noticed you crossposted in both SQL Server and Oracle groups, but both have many proprietary additions (or even changes) to the ANSI standard SQL syntax.
• What is the actual structure of your table. Please post your DDL (CREATE TABLE statements, including all constraints) for all tables that are relevant for the query. Irrelevant columns may be omitted.
• Give some sample data. Do so in the form of INSERT statements. I love to cut and paste your statements, so I can run some tests. I hate to do lots of typing myself. Remember that I, and many others, are helping you and others in our free time - don't make us spend more of our time than necessary!
• Tell us what output you expect, based on the sample data you provided. Explain why that should be the output and not anything else. Don't forget to include the formulas used.
• Explain the business problem behind your question.

The last part (the business problem) is the only thing I can distill from your message. If you provide the rest, I'm sure I (or someone else) will be able to help you out.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)