SQL - Execute SQL query on SQL query

Question

Is it possible to run a SQL query on another SQL query? I have a SQL query that compiles many columns into one using a Union All clause. I need to use a group clause but you will see that that is not possible. Is there any way to run another SQL query on this one?

Query:

Select Ins1 as Insurance

From InsAuth2
WHERE Ins1 IS NOT NULL
Group By Ins1
Union All
Select Ins2 as Insurance
From InsAuth2
WHERE Ins2 IS NOT NULL
Group By Ins2
Union All
Select Ins3 as Insurance
From InsAuth2
WHERE Ins3 IS NOT NULL
Union All
Select Ins4 as Insurance
From InsAuth2
WHERE Ins4 IS NOT NULL
Union All
Select Ins5 as Insurance
From InsAuth2
WHERE Ins5 IS NOT NULL

I need to be able to select unique values from this compiled column. I could use a group by in every statement, however that would only return unique values from the original column. The compiled column could potentially not have all unique values if values unique to one column were also unique to another. Therefore I must have another SQL query operating on this one. Any suggestions?

Yes you can. Just do an outer query with group by on appropriate column — nawfal
– nawfal, Commented Jul 25, 2012 at 14:54
You can execute a query on a list returned from select if you just put your query in From. — Black
– Black, Commented May 30, 2018 at 13:03

jtimperley · Accepted Answer · 2012-07-25 14:54:53Z

6

Use an inner query i.e.

SELECT *
FROM
(
    YourQueryHere
) Inn
GROUP BY YourColumnHere

answered Jul 25, 2012 at 14:54

jtimperley

2,55414 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikola Markovinović · Accepted Answer · 2012-07-25 14:54:12Z

3

Use derived table and distinct:

select distinct Insurance
from
(
  Select Ins1 as Insurance
  From InsAuth2
  WHERE Ins1 IS NOT NULL
  Union All
  Select Ins2 as Insurance
  From InsAuth2
  WHERE Ins2 IS NOT NULL
  ...
) table_alias

answered Jul 25, 2012 at 14:54

Nikola Markovinović

19.4k5 gold badges48 silver badges53 bronze badges

Comments

Matt Smucker · Accepted Answer · 2012-07-25 14:59:45Z

3

If you use UNION instead of UNION ALL it will filter out duplicates

Select Ins1 as Insurance
From InsAuth2
WHERE Ins1 IS NOT NULL
Group By Ins1
Union
Select Ins2 as Insurance
From InsAuth2
WHERE Ins2 IS NOT NULL
Group By Ins2
Union
Select Ins3 as Insurance
From InsAuth2
WHERE Ins3 IS NOT NULL
Union
Select Ins4 as Insurance
From InsAuth2
WHERE Ins4 IS NOT NULL
Union
Select Ins5 as Insurance
From InsAuth2
WHERE Ins5 IS NOT NULL

answered Jul 25, 2012 at 14:59

Matt Smucker

5,2442 gold badges24 silver badges36 bronze badges

Comments

Jahirul · Accepted Answer · 2016-08-10 08:54:28Z

0

Try using UNION instead of UNION ALL

Thanks

answered Aug 10, 2016 at 8:54

Jahirul

251 silver badge7 bronze badges

3 Comments

Inzimam Tariq IT Over a year ago

should be a comment.

Jahirul Over a year ago

Try the following:

Jahirul Over a year ago

Try the following: Select Ins1 as Insurance From InsAuth2 WHERE Ins1 IS NOT NULL Union Select Ins2 as Insurance From InsAuth2 WHERE Ins2 IS NOT NULL Union Select Ins3 as Insurance From InsAuth2 WHERE Ins3 IS NOT NULL Union Select Ins4 as Insurance From InsAuth2 WHERE Ins4 IS NOT NULL Union Select Ins5 as Insurance From InsAuth2 WHERE Ins5 IS NOT NULL

Collectives™ on Stack Overflow

SQL - Execute SQL query on SQL query

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related