Several Counts and grouping the results...

[daveng]

Hi,

I'm trying to get some information from several tables.

Basically I have 3 tables :

Call_Activity Table with 3 fields : Activity_ID, User_ID, Subject
User Table with 2 fields; User_ID, Name
PhoneCall Table with 2 fileds : Activity_ID, Complete_Flag

(there is more fields than that, but that's the ones I'm interrested in this particular case). I can't change the design of the database because I'm using an existing application and it's preconfigured database.

The idea that people are entering their phone activity in the base. When someone places a call it creates a new item in the Call_Activity table, linked to the user making that call (the user is listed in the User table). If the call is completed, the PhoneCall table set the Complete_Flag to true.

I want to get make a unique SQL query to display the following :

Names                 # of calls           # of completed calls
John                     3                       2
Paul                     12                     10

That would means that John made 3 calls with 2 of which completed. That would means that the table would look like (just displaying the info about John's call) :

User Table

User_ID   Name
1             John

Activity Table

Activity_ID    User_ID   Subject
1               1           Call1
2               1           Call2
3               1           Call3

PhoneCall Table

Activity_ID    Complete_Flag
1                  1
2                  0
1                  1

Anyone has an idea how to do this in just on SQL query?
I've been trying different things with no luck.
Thanks!!

[amitpatel66]

Hi,

I'm trying to get some information from several tables.

Basically I have 3 tables :

Call_Activity Table with 3 fields : Activity_ID, User_ID, Subject
User Table with 2 fields; User_ID, Name
PhoneCall Table with 2 fileds : Activity_ID, Complete_Flag

(there is more fields than that, but that's the ones I'm interrested in this particular case). I can't change the design of the database because I'm using an existing application and it's preconfigured database.

The idea that people are entering their phone activity in the base. When someone places a call it creates a new item in the Call_Activity table, linked to the user making that call (the user is listed in the User table). If the call is completed, the PhoneCall table set the Complete_Flag to true.

I want to get make a unique SQL query to display the following :

Names                 # of calls           # of completed calls
John                     3                       2
Paul                     12                     10

That would means that John made 3 calls with 2 of which completed. That would means that the table would look like (just displaying the info about John's call) :

User Table

User_ID   Name
1             John

Activity Table

Activity_ID    User_ID   Subject
1               1           Call1
2               1           Call2
3               1           Call3

PhoneCall Table

Activity_ID    Complete_Flag
1                  1
2                  0
1                  1

Anyone has an idea how to do this in just on SQL query?
I've been trying different things with no luck.
Thanks!!

try this query:

 
SELECT u.name, (SELECT count(*) FROM activity WHERE user_id = u.user_id) No_of_Calls, (SELECT COUNT(*) FROM phonecall WHERE activity_id IN ( SELECT activity_id FROM activity WHERE user_id = u.user_id) AND complete_flag = 1) Calls_Completed FROM
user u
 

[VijaySofist]

Hi!

Please dont use the table name user because it is a System table that will be defaultly available in the SQL Server. If we use it it will display error. Anyway way we can solve that error by giving double quotes to the table name user (ex: "user").

Anyway copy the following query and paste it in your query analyser. It will work.

SELECT u.name, (SELECT count(*) FROM call_activity WHERE user_id = u.user_id) No_of_Calls, (SELECT COUNT(*) FROM phonecall WHERE activity_id IN ( SELECT activity_id FROM call_activity WHERE user_id = u.user_id) AND complete_flag = 1) Calls_Completed FROM
"user" u

All the Best

With Regards
Vijay. R

[daveng]

Thanks guys.
I'm going to try all this.

Actually, I'm not using a table named user, it's just the name I used for the description of my setting.

Many thanks again!

[daveng]

It worked, I just had to add a GROUP by name to have only one line per user.

Thanks for your help, it saved me a lot of time!