I have two SQL statements (shown below). They both have label and id fields. However, because the second select uses a count on a column to find the number of processors, if I create a single view from them I think the count will then be incorrect.
select COMPUTER.LABEL, COMPUTER.ID, COMPUTER.OS_NAME, SWARE_SIG.SWARE_DESC, from MATCHED_SWARE,SWARE_SIG,COMPUTER where MATCHED_SWARE.ID = COMPUTER.ID and MATCHED_SWARE.SWARE_SIG_ID = SWARE_SIG.SWARE_SIG_ID select COMPUTER.LABEL, COMPUTER.ID, PROCESSOR.PROCESSOR_ID, count(*) as NUM_PROCESSOR from COMPUTER,PROCESSOR where COMPUTER.ID = PROCESSOR.ID group by COMPUTER.LABEL, COMPUTER.ID, PROCESSOR.PROCESSOR_ID
How can I create a single query that returns both sets of information in one report?
The easiest way to combine those two queries is with a UNION of their result sets. In order to make the combined results usable, it's important to:
include some column which identifies, for each row, the SELECT that produced it
ensure that each SELECT produces the same number of columns
ensure that all columns of each SELECT are UNION-compatible
Here is a UNION that should work for you:
select COMPUTER.LABEL , COMPUTER.ID , 'Operating System' as rowtype , COMPUTER.OS_NAME as rowvalue1 , SWARE_SIG.SWARE_DESC as rowvalue2 from COMPUTER inner join MATCHED_SWARE on COMPUTER.ID = MATCHED_SWARE.ID inner join SWARE_SIG on MATCHED_SWARE.SWARE_SIG_ID = SWARE_SIG.SWARE_SIG_ID union all select COMPUTER.LABEL , COMPUTER.ID , 'Processor ' as rowtype , PROCESSOR.PROCESSOR_ID as rowvalue1 , cast(count(*) as varchar) as rowvalue2 from COMPUTER inner join PROCESSOR on COMPUTER.ID = PROCESSOR.ID group by COMPUTER.LABEL , COMPUTER.ID , PROCESSOR.PROCESSOR_ID
The result set that you get back from the UNION will have a column called "rowtype" which will distinguish which SELECT the row came from. This is often necessary but occasionally superfluous (as in this case, most likely).
UNION-compatible means that you must align numbers with numbers, strings with strings, and datetimes with datetimes. In this example, the column in the result set called "rowvalue2" will have string values, hence it is necessary to CAST the numeric count as VARCHAR.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading