ask for sql

Question

I need to output the number when the audio_invalid status of each type of PID is invalid and valid in the output table1, and query the total number of each type of PID with table2. The total amount of data in table1 and table2 is inconsistent, and the amount of data in table1> table2

the result like the below pic:

Answer 1

Hi @Davis ，

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables(table1 and table2) together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

I created some sample data and please refer below and check whether it is helpful to you.

create table table1  
(project_id varchar(20),  
person_id varchar(20),  
audio_valid varchar(10))  
  
insert into table1 values  
(397,'Abc','valid'),  
(397,'Abc','valid'),  
(397,'Abc','invalid'),  
(397,'Cba','valid'),  
(397,'Cba','invalid'),  
(397,'Ccc','invalid')  
  
create table table2  
(project_id varchar(20),  
person_id varchar(20),  
delivery_date date)  
  
insert into table2 values  
(397,'Abc','2021-08-01'),  
(397,'Abc','2021-08-02'),  
(397,'Abc','2021-08-03'),  
(397,'Cba','2021-08-11'),  
(397,'Cba','2021-08-12')  
  
;with cte as (  
select project_id,person_id,  
sum(case when audio_valid='valid' then 1 else 0 end) valid_total,  
sum(case when audio_valid='invalid' then 1 else 0 end) invalid_total  
from table1  
group by project_id,person_id)  
,cte1 as (  
select project_id,person_id,count(delivery_date) delivery_sum  
from table2   
group by project_id,person_id)  
select a.project_id,a.person_id,a.valid_total,a.invalid_total,b.delivery_sum   
from cte a   
left join cte1 b on a.project_id=b.project_id and a.person_id=b.person_id  

Output:

project_id	person_id	valid_total	invalid_total	delivery_sum  
397	Abc	2	1	3  
397	Cba	1	1	2  
397	Ccc	0	1	NULL  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.