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? 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)
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)
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?
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?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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,...
|
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"...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |