stankstank
asked on
Query just doesn't return the right data...
out of three tables in my database, I am trying to determine 1) the total number of calls made per user, 2) the total number of demos set per user and 3) the total number of those demos which were turned into sales, per user. My table layout can be found at: http://76.12.52.157/temp/tables.png Please note: The join may not be completely correct as there are several columns in each table that relate to another table.. I will describe more below. :)
The [Appusers].Users table (u) holds all of the telemarketers. The [AppCalls].PhoneCalls table has a FK from the users table which allows us to keep track of all of the phone calls made by any given telemarketer. When the phone call record has a FK_Status_ID of 20, then the call was 'Completed'.
When an appointment is set, it saves the record in the demos (d) table. When the demo_status_id = 180, the demo was sold... The total number of records in that table related to the current user gives us the total number of demos set, per telemarketer (user).
I have been fooling wiht this query for entirely too long - it gives me CRAZY numbers! For example, there are only 800 phone calls and 73 demos in the database... This crazy query returns more than 12,000 phone calls and upwards of 5,000 demos! UGH!
Can you see the problem I am having? I have commented out parts of and mangled my query to the point that I am completely lost...
Please help! Thank you in advance, experts. :)
The [Appusers].Users table (u) holds all of the telemarketers. The [AppCalls].PhoneCalls table has a FK from the users table which allows us to keep track of all of the phone calls made by any given telemarketer. When the phone call record has a FK_Status_ID of 20, then the call was 'Completed'.
When an appointment is set, it saves the record in the demos (d) table. When the demo_status_id = 180, the demo was sold... The total number of records in that table related to the current user gives us the total number of demos set, per telemarketer (user).
I have been fooling wiht this query for entirely too long - it gives me CRAZY numbers! For example, there are only 800 phone calls and 73 demos in the database... This crazy query returns more than 12,000 phone calls and upwards of 5,000 demos! UGH!
Can you see the problem I am having? I have commented out parts of and mangled my query to the point that I am completely lost...
Please help! Thank you in advance, experts. :)
SELECT u.NameFirst + ' ' + u.NameLast as Telemarketer,
SUM(CASE pc.FK_Status_ID WHEN '20' THEN 1 ELSE 0 END) as [Completed Calls],
SUM(CASE d.Demo_Status_ID WHEN '180' THEN 1 ELSE 0 END) as [Demos Sold]
-- [Demos Set] should be the total number of records related to the user
FROM AppUsers.Users AS u INNER JOIN
AppCalls.PhoneCalls AS pc ON u.User_Auto_ID = pc.FK_AssignedToUser_ID INNER JOIN
AppDemos.Demos AS d ON u.User_Auto_ID = d.FK_SetBy_UserID
WHERE u.FK_App_ID = 6 AND u.FK_Office_ID = @OfficeID
GROUP BY u.NameFirst, u.NameLast
That query should return a result set like this:
Telemarketer Completed Calls Demos Sold Demos Set
---------------------------------------------------------
Mary Jones 401 48 80
Katie Janes 540 74 104
Instead of using the Sum function did you try using the COUNT function ?
ASKER
>> Instead of using the Sum function did you try using the COUNT function ?<<
Every time I used it, I get a count for all rows, not per user... Having said that, the problem isn't SUM instead of count(*), is it?
Every time I used it, I get a count for all rows, not per user... Having said that, the problem isn't SUM instead of count(*), is it?
ASKER
Regardless of whether I use SUM or COUNT, it still does not return data properly... Something is going on with a JOIN statement or something... There are only 3 users, around a thousand phone calls and less than 100 demos in the database. Yet, when I run select count(*) and the FROM statement in the query, I receive a count of over 225,000 records. This is NOT right! Please help - I can tweak the query later, I just need to make it work now...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>Something is going on with a JOIN statement or something
That's right. You get 1 row for every user call multiplied by 1 row for every user demo. In other words, the join has a multiplicative effect. You want it to have an additive effect--do the calculations separately and union them together. :>)
That's right. You get 1 row for every user call multiplied by 1 row for every user demo. In other words, the join has a multiplicative effect. You want it to have an additive effect--do the calculations separately and union them together. :>)
ASKER
dgmg - Thank you for your post.. I ran it and after changing a few values it seems to work. I knew the numbers were not adding up properly, but i just couldn't tell what it was! Can you give me a term to search for regarding this? I have a limited understanding of the multiplication effect, but I knew something was going on.
I am not at home now, I will replly with the results as to whetehr or not the query is returning accurate results when I get home in a few hours... Having said that - they look MUCH better. Instead of reporting close to 5,000 records, now they are down to the few hundred htey are suppose to be!
Thanks again, I will reply when I have tested it thoroughally later.
StankStank
I am not at home now, I will replly with the results as to whetehr or not the query is returning accurate results when I get home in a few hours... Having said that - they look MUCH better. Instead of reporting close to 5,000 records, now they are down to the few hundred htey are suppose to be!
Thanks again, I will reply when I have tested it thoroughally later.
StankStank
Not sure what the term might be. But, I'll try to explain by example.
Suppose you have a dance with 5 girls and 5 boys. If you just count the boys at the ance you get 5. If you just count the girls at the dance you get 5. However, if you try to count potential couples, then you get 25 because each boy can dance with 5 different girls.
A join between the girl table (5 rows) and the boy table (5 rows) returns 25 rows. So, if you group by boy and count the girls each boy will have 5 and the sum will be 25. Same if you group by girls and sum the count of boys.
But, if you count boys and girls independently, you get 5 boys and 0 girls in one table. you get 0 boys and 5 girls in the other. When you sum over the union of those results you get 5 each.
Suppose you have a dance with 5 girls and 5 boys. If you just count the boys at the ance you get 5. If you just count the girls at the dance you get 5. However, if you try to count potential couples, then you get 25 because each boy can dance with 5 different girls.
A join between the girl table (5 rows) and the boy table (5 rows) returns 25 rows. So, if you group by boy and count the girls each boy will have 5 and the sum will be 25. Same if you group by girls and sum the count of boys.
But, if you count boys and girls independently, you get 5 boys and 0 girls in one table. you get 0 boys and 5 girls in the other. When you sum over the union of those results you get 5 each.
ASKER
I would rather just dance with the girls. :) Thank you for your help, I understand what you are saying. I am still unsure when to use union as not all of my joins have this result... I guess I understand why it does it, I just don't understand when..
Thanks again for your help - the query is working perfectly!
StankStank
Thanks again for your help - the query is working perfectly!
StankStank
ASKER
Great answer, expert help! Thank you!