1. Registered User
Join Date
Jan 2013
Posts
22

Hey Guys,

But I am trying to write a query to figure out how much time we actually take on a project overall based on the number of total number of Qs.

I have writing the following query to do the just that:

Code:
```SELECT CBT.[Project Title], Sum(CBT.[Time Take]) AS [Total Time Taken], Max(GlobalPipe.[# Qs]) AS [Total Qs], Max(GlobalPipe.[# Qs]) AS [Total Qs] / Sum(CBT.[Time Take]) AS [Total Time Taken] AS [Time Per Question]
FROM CBT INNER JOIN GlobalPipe ON (CBT.[Project Title]=GlobalPipe.[CBT Name]) AND (CBT.[Writer Name]=GlobalPipe.[Writer 1]) AND (CBT.Region=GlobalPipe.Region)
GROUP BY CBT.[Project Title];```
I would really appericate if you anyone to let me know where am I going wrong with this.

Thank you again!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
well to tell us what isn't working might be a good starting point
bearing in mind we don't know how your tables are designed and what they relate to
we also don't know why your query isn't working, and we cant find out for ourselvesd because there is no test data.

unfortunately my crystal ball is being given its annual service so its kinda difficult to diagnose where you are going wrong with this

3. Registered User
Join Date
Dec 2013
Posts
46
nbkn8ct,

Is this closer? (TotalTime, TotalQuestions, AverageTime)

Code:
```SELECT CBT.[Project Title],
Sum(CBT.[Time Take]) AS [Total Time Taken],
Count(GlobalPipe.[# Qs]) AS [Total Qs],
Avg(CBT.[Time Take]) AS [Time Per Question]
FROM CBT INNER JOIN GlobalPipe ON
CBT.[Project Title] = GlobalPipe.[CBT Name] AND
CBT.[Writer Name] = GlobalPipe.[Writer 1] AND
CBT.Region = GlobalPipe.Region
GROUP BY CBT.[Project Title];```
hth,
Wayne

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•