Combine 2 Queries including totals in a single query

[kalebson]

Hi all,

Im probably just drawing a blank here. Im hoping there is a way to do this. I have 2 queries w/ the following fields:

Date
Skillset
Calls Ans
AnsAft Thresh
Answer Delay
Talk Time
Not Ready
Site(Dont need this)
Skillset Type

Now heres what I want to do:

I want to use the Date, Skillset and Skillset Type fields from only one of the queries because the other query does not have Sundays as only one site works Sundays and not all Skillsets are available at the other site.

I want to total:

Calls Ans
AnsAft Thresh
Answer Delay
Talk Time
Not Ready

Where the skillset for that date matches in both queries for that date:

So basically if on 1/2/06 Skillset1 is in both queries I want a total of the above fields for that date. I do not want to include site in this query at all as they are just a location determining factor.

Any help would be great Ive been banging my head on my desk here trying all these join SQL queries and getting parameter errors 8).

TIA

 

[lespaul]

can you post the SQL of the two queries please?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[kalebson]

SELECT SkillsetDataPHX.Date, SkillsetDataPHX.Skillset, SkillsetDataPHX.[Calls Ans], SkillsetDataPHX.[AnsAft Thresh], SkillsetDataPHX.[Answer Delay], AHTDataPHX.[Talk Time], AHTDataPHX.[Not Ready], SkillsetDataPHX.Site, SkillsetType.[Skillset Type]
FROM (SkillsetDataPHX INNER JOIN AHTDataPHX ON (SkillsetDataPHX.Site = AHTDataPHX.Site) AND (SkillsetDataPHX.Skillset = AHTDataPHX.Skillset) AND (SkillsetDataPHX.Date = AHTDataPHX.Date)) INNER JOIN SkillsetType ON SkillsetDataPHX.Skillset = SkillsetType.Skillset
ORDER BY SkillsetDataPHX.Date, SkillsetDataPHX.Skillset;

*********************************************************

SELECT SkillsetDataSTP.Date, SkillsetDataSTP.Skillset, SkillsetDataSTP.[Calls Ans], SkillsetDataSTP.[AnsAft Thresh], SkillsetDataSTP.[Answer Delay], AHTDataSTP.[Talk Time], AHTDataSTP.[Not Ready], SkillsetDataSTP.Site, SkillsetType.[Skillset Type]
FROM (AHTDataSTP INNER JOIN SkillsetDataSTP ON (AHTDataSTP.Site = SkillsetDataSTP.Site) AND (AHTDataSTP.Skillset = SkillsetDataSTP.Skillset) AND (AHTDataSTP.Date = SkillsetDataSTP.Date)) INNER JOIN SkillsetType ON (AHTDataSTP.Skillset = SkillsetType.Skillset) AND (SkillsetDataSTP.Skillset = SkillsetType.Skillset)
ORDER BY SkillsetDataSTP.Date, SkillsetDataSTP.Skillset;

There ya go

 

[kalebson]

Remember I want to eliminate the [Site] from the combination as its a distinct field.

 

[lespaul]

you want a count of each of these fields by date, right?
this may be what you're looking for:

SELECT A.[Date], COUNT(A.[Calls Ans]), COUNT(A.[AnsAft Thresh]), COUNT(A.[Answer Delay]), COUNT(A.[Talk Time]), COUNT(A.[Not Ready])
FROM (SELECT * FROM SkillSetDataPHX P INNER JOIN SkillsetDataSTP S ON P.[Date] = S.[Date] AND P.SkillSet = S.SkillSet) As A
GROUP BY A.[Date]

You should try to avoid naming a field "DATE", it's a reserved keyword in most languages and can cause issues when trying to retrieve.

HTH

Leslie

 

[kalebson]

I dont think i need a count. I want to achieve this...

Example: Just gonna do 4 fields in example

Query1
Date Skillset Calls Ans AnsAft Thresh
01/01/2006 Skillset1 30 5
01/02/2006 Skillset1 40 4

Query2 Note:01/01/2006 wont exist it was Sun
Date Skillset Calls Ans AnsAft Thresh
01/02/2006 Skillset1 25 5


ComboQuery
Date Skillset Calls Ans AnsAft Thresh
01/01/2006 Skillset1 30 5 (from Query1 sonce not in Query2)
01/02/2006 Skillset1 65 9 (In both so totaled)

I hope this helps.. Im not sure about count.

 

[lespaul]

so you want a SUM:


SELECT A.[Date], SUM(A.[Calls Ans]), SUM(A.[AnsAft Thresh]), SUM(A.[Answer Delay]), SUM(A.[Talk Time]), SUM(A.[Not Ready])
FROM (SELECT * FROM SkillSetDataPHX P INNER JOIN SkillsetDataSTP S ON P.[Date] = S.[Date] AND P.SkillSet = S.SkillSet) As A
GROUP BY A.[Date]

 

[kalebson]

It doesnt like the A. Says it could be more than one of the tables listed. THink I had that error before hehe.

 

[lespaul]

can you post the SQL you tried that gave the error?

 

[kalebson]

I just copy/pasted what you put there. I just said the error looked familiar.

 

[lespaul]

what happens if you just run:

SELECT * FROM SkillSetDataPHX P INNER JOIN SkillsetDataSTP S ON P.[Date] = S.[Date] AND P.SkillSet = S.SkillSet



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[kalebson]

That runs fine however it does not include ALL data for some reason. Also note SkillsetDataPHX and STP are Tables. Im trying to combine the 2 queries that I made from these tables. There are a total of 4 tables, 2 for STP and 2 for PHX. I was trying not to combine the 4 tables into 2 tables so I would not have to do more appending and have the same data 2 times.

PHXAnsweredData (Query)From above
SELECT SkillsetDataPHX.Date, SkillsetDataPHX.Skillset, SkillsetDataPHX.[Calls Ans], SkillsetDataPHX.[AnsAft Thresh], SkillsetDataPHX.[Answer Delay], AHTDataPHX.[Talk Time], AHTDataPHX.[Not Ready], SkillsetDataPHX.Site, SkillsetType.[Skillset Type]
FROM (SkillsetDataPHX INNER JOIN AHTDataPHX ON (SkillsetDataPHX.Site = AHTDataPHX.Site) AND (SkillsetDataPHX.Skillset = AHTDataPHX.Skillset) AND (SkillsetDataPHX.Date = AHTDataPHX.Date)) INNER JOIN SkillsetType ON SkillsetDataPHX.Skillset = SkillsetType.Skillset
ORDER BY SkillsetDataPHX.Date, SkillsetDataPHX.Skillset;

STPAnsweredData (Query) From above
SELECT SkillsetDataSTP.Date, SkillsetDataSTP.Skillset, SkillsetDataSTP.[Calls Ans], SkillsetDataSTP.[AnsAft Thresh], SkillsetDataSTP.[Answer Delay], AHTDataSTP.[Talk Time], AHTDataSTP.[Not Ready], SkillsetDataSTP.Site, SkillsetType.[Skillset Type]
FROM (AHTDataSTP INNER JOIN SkillsetDataSTP ON (AHTDataSTP.Site = SkillsetDataSTP.Site) AND (AHTDataSTP.Skillset = SkillsetDataSTP.Skillset) AND (AHTDataSTP.Date = SkillsetDataSTP.Date)) INNER JOIN SkillsetType ON (AHTDataSTP.Skillset = SkillsetType.Skillset) AND (SkillsetDataSTP.Skillset = SkillsetType.Skillset)
ORDER BY SkillsetDataSTP.Date, SkillsetDataSTP.Skillset;

 

[lespaul]

SELECT A.[Date], SUM(A.[Calls Ans]), SUM(A.[AnsAft Thresh]), SUM(A.[Answer Delay]), SUM(A.[Talk Time]), SUM(A.[Not Ready])
FROM (SELECT * FROM PHXAnsweredData P INNER JOIN STPAnsweredData S ON P.[Date] = S.[Date] AND P.SkillSet = S.SkillSet) As A
GROUP BY A.[Date]

should work now that the sub query has the QUERY names not the TABLE names.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[kalebson]

I wish it did..Same error. Says it could be more than one of the tables listed..although they arent actually tables this time. Also the data for STP for 1/1 is not showing up because we are trying to match on date and skillset. The STP will have unique records for the dates and skillsets not included in PHX. It just plain dont like the "A".

 

[kalebson]

well I got these 3 fields to work so far:

Date
Skillset
Skillset Type

by using this:

SELECT STPAnsweredData.Date, STPAnsweredData.Skillset, STPAnsweredData.[Skillset Type]
FROM STPAnsweredData LEFT JOIN PHXAnsweredData ON (STPAnsweredData.Skillset = PHXAnsweredData.Skillset) AND (STPAnsweredData.Date = PHXAnsweredData.Date)
ORDER BY STPAnsweredData.Date, STPAnsweredData.Skillset;


Now I just need the Sum fields added to that. I get an error that Date is not aggragate function if I try to add anything else. It says I should make a seperate query for that and add it to this query. Not sure how to do that one. Seems I do have to make a Left Join to get all the info tho.

 

[lespaul]

the LEFT JOIN says: Get all the data from STPAnsweredData and only those that match from PHXAnsweredData.

(check out Understanding SQL Joins for more information)

SELECT A.[Date], SUM(A.[Calls Ans]), SUM(A.[AnsAft Thresh]), SUM(A.[Answer Delay]), SUM(A.[Talk Time]), SUM(A.[Not Ready])
FROM (SELECT * FROM STPAnsweredData S LEFT JOIN PHXAnsweredData P ON S.[Date] = A.[Date] AND S.SkillSet = P.SkillSet) As A
GROUP BY A.[Date]

if you need to add SkillSet to the SELECT clause you will also need to add it to the GROUP BY clause.

Every field in the SELECT that is not an aggregate function has to be in the GROUP BY:

SELECT Feild1, SUM(Field2) From tableName GROUP BY Field1

SELECT Field1, Field2, Field3, Sum(Field4) from TableName Group By Field1, Field2, Field3


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[kalebson]

Sweet I got it Leslie...Thx a ton..Now I just gotta get 0's where the values are Null hehe.

 

[lespaul]

look at the NZ function

NZ(SomeField, ValueifNull)

Glad you got it!

have a great weekend.
Leslie

 

[kalebson]

Cool I found that in help file..I was trying IFNULL but that only seems to work on MySQL. THanks again for all your help.