CODEWITHSUNDEEP
sqloracle-sqldeveloper
user3037266
user3037266

Reputation: 3

How to show multiple query results in one view?

I'm trying to show results from multiple select queries in one view. I've tried to google it without success. Any suggestions?

Example:

Query 1

SELECT REGEXP_COUNT( column1, '</Expression>' ) AS Col1
  FROM table1 
 WHERE column1 like '%Group1%'

The query gives something like this:

Col1
1000
1000
1000
135

Query 2

SELECT REGEXP_COUNT( column1, '</Expression>' ) AS Col2
  FROM table1
 WHERE column1 like '%Group2%'

Results in:

Col2
1000
1000
1000
1000
1000
46

I want to show the count results of both queries in one view with separate columns. Also the results are shown in multiple rows instead of one. How can I sum up the numbers of all rows? All suggestions appreciated! The final result should be something like this

Col1 | Col2
3135 | 5046

Upvotes: 0

Views: 2606

Answers (1)

tvm
tvm

Reputation: 3449

Perhaps UNION or UNION ALL would help?

SELECT REGEXP_COUNT( column1, '</Expression>' )
  FROM table1
 WHERE column1 like '%Group1%'
UNION ALL
SELECT REGEXP_COUNT( column1, '</Expression>' )
  FROM table1
 WHERE column1 like '%Group2%';

If you really need columns, perhaps something like:

SELECT
  A.cnt1,
  b.cnt2
FROM
  (
    SELECT
      REGEXP_COUNT( column1, '</Expression>' ) cnt1,
      ROWNUM AS rwn
    FROM
      table1
    WHERE
      column1 LIKE '%Group1%'
  )
  a
FULL OUTER JOIN
  (
    SELECT
      REGEXP_COUNT( column1, '</Expression>' ) cnt2,
      ROWNUM AS rwn
    FROM
      table1
    WHERE
      column1 LIKE '%Group2%'
  )
  b
ON
  a.rwn = b.rwn;

And for the SUMS that you added, you can again use simple subselects in SELECT.

SELECT
  (
    SELECT
      SUM(REGEXP_COUNT( column1, '</Expression>' )) AS Col1
    FROM
      table1
    WHERE
      column1 LIKE '%Group1%'
  ) AS res1,
  (
    SELECT
      SUM(REGEXP_COUNT( column1, '</Expression>' )) AS Col1
    FROM
      table1
    WHERE
      column1 LIKE '%Group2%'
  ) AS res2
FROM
dual;

Upvotes: 1

Related Questions