How to create a new table to save calculated outputs from the original table in SQLite?-CodePudding

I am a new member here, and hoping this question is clear (feedback is greatly appreciated). In SQLite, I am trying to create a new table after calculating a, b, c, and d. The outputs are listed below in the code. The original table is covid2021. At first, I wanted to filter out the year and group by PTID for a and c. For b and d, I filtered out the year and PTID and DIAGNOSIS_CD. The output for a, b, c, and d are the number of rows and are shown in the code. My goal is to create a new table combining these four outputs. The covid2021 has 35,707 rows originally. After finding and adding a, b, c, and d for each output, the total rows would be 17,818. Is it possible to create a new table where the new number of rows is 17,818 at the end? If so, how would I do that? I appreciate the help.

Here is my code below:

CREATE TABLE covid2021 AS
SELECT *
FROM covid
WHERE RESULT_DATE1 LIKE ' 21%' AND diagdate1 LIKE ' 21%'
ORDER BY PTID
;

/*Finding the output for a,b,c,d*/
/*a*/
SELECT *
FROM covid2021
WHERE TEST_RESULT='POSITIVE' AND DIAGNOSIS_CD='U071' /*AND ENCID <>''*/AND RESULT_DATE1 LIKE ' 21%' AND diagdate1 LIKE ' 21%'
GROUP BY PTID
ORDER BY PTID ASC
;
/*Output: 8654*/

/*c*/
SELECT *
FROM covid2021
WHERE TEST_RESULT='NEGATIVE' AND DIAGNOSIS_CD='U071' AND RESULT_DATE1 LIKE ' 21%' AND diagdate1 LIKE ' 21%'
GROUP BY PTID
ORDER BY PTID
;
/*Output: 9087 */

/*d*/
SELECT */*, MAX(RESULT_DATE1), COUNT(*) as Count/*, MIN(RESULT_DATE), MIN(RESULT_TIME)*/
FROM covid2021
WHERE TEST_RESULT='NEGATIVE' AND DIAGNOSIS_CD <>'U071' AND RESULT_DATE1 LIKE ' 21%' AND diagdate1 LIKE ' 21%'
GROUP BY DIAGNOSIS_CD || PTID
ORDER BY PTID
;
/*Output: 54 */

/*b*/
SELECT */*, MAX(RESULT_DATE1), COUNT(*) as Count/*, MIN(RESULT_TIME)*/
FROM covid2021
WHERE TEST_RESULT='POSITIVE' AND DIAGNOSIS_CD IS NOT 'U071' AND RESULT_DATE1 LIKE ' 21%' AND diagdate1 LIKE ' 21%'
GROUP BY DIAGNOSIS_CD || PTID
ORDER BY PTID
;
/*Output: 23 */

CodePudding user response：

If your queries produce the same columns, then UNION them together:

create table my_new_table as (
  query_1
  UNION
  query_2
  UNION
  query_3
  UNION
  query_4);