473,325 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Help with 2 queries / Join problem

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?
Jul 20 '05 #1
4 1496
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)
Jul 20 '05 #2
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)
Jul 20 '05 #3
fn
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.employee GROUP BY employee) as ActiveProjectCount,
(SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
TotalProjectCountPerEmployee
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:
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?


Jul 20 '05 #4
fn
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:
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.employee GROUP BY employee) as ActiveProjectCount,
(SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
TotalProjectCountPerEmployee
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:
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?



Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Alexandre MELARD | last post by:
Hi, My name is alexandre, I am 4th year student at the Napier university of edinburgh. I am finishing my year and do a presentation of my honours project next wednesday (the 5th of May). I am...
6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
7
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
1
by: mithril | last post by:
I cannot get my head around this query... I think I need a nested query but here's the problem. I promise i've exerted my meager brain power on this problem & ask this as a last resot! 3...
1
by: prabhukalyan | last post by:
Hi all, I am not so good in queries. here is my problem 2 tables to store the received items (fabric)-- inwardmaster, inwarddetails and after some processing (Dyeing) the items were...
1
by: loosecannon_1 | last post by:
Hello everyone, I am hoping someone can help me with this problem. I will say up front that I am not a SQL Server DBA, I am a developer. I have an application that sends about 25 simultaneous...
0
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in...
0
crystal2005
by: crystal2005 | last post by:
Hi, I am having trouble with some complex SQL queries. I’ve got winestore database, taken from Web Database Application with PHP and MySQL book. And some question about queries as the following ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.