473,380 Members | 1,268 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,380 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 1498
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 ...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
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...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.