Help with 2 queries / Join problem

[dwight0]

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_d ate
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_d ate

Any ideas?

[Hugo Kornelis]

Re: Help with 2 queries / Join problem

On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:
[color=blue]
>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_d ate
>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_d ate
>
>Any ideas?[/color]

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)

[Hugo Kornelis]

Re: Help with 2 queries / Join problem

On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:
[color=blue]
>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_d ate
>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_d ate
>
>Any ideas?[/color]

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)

[fn]

Re: Help with 2 queries / Join problem

If I had to do it, I'd probably start with the following:

SELECT DISTINCT employee,
(SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
= maintable.emplo yee GROUP BY employee) as ActiveProjectCo unt,
(SELECT COUNT(*) FROM projectdb WHERE employee = maintable.emplo yee) AS
TotalProjectCou ntPerEmployee
FROM projectdb AS maintable

which would yield something like:

Joe 5 10
Mary 7 8
Brian Null 1

I would then look at that Null, say Naaaah... and go about doing it right :)

FN



dwight0 wrote:
[color=blue]
> 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_d ate
> 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_d ate
>
> Any ideas?[/color]

[fn]

Re: Help with 2 queries / Join problem

Oh, I may also look at that first subquery and wonder why the heck I put
a GROUP BY clause when I didn't need one.

FN

fn wrote:
[color=blue]
> If I had to do it, I'd probably start with the following:
>
> SELECT DISTINCT employee,
> (SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
> = maintable.emplo yee GROUP BY employee) as ActiveProjectCo unt,
> (SELECT COUNT(*) FROM projectdb WHERE employee = maintable.emplo yee) AS
> TotalProjectCou ntPerEmployee
> FROM projectdb AS maintable
>
> which would yield something like:
>
> Joe 5 10
> Mary 7 8
> Brian Null 1
>
> I would then look at that Null, say Naaaah... and go about doing it
> right :)
>
> FN
>
>
>
> dwight0 wrote:
>[color=green]
>> 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_d ate
>> 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_d ate
>>
>> Any ideas?[/color]
>
>[/color]