Implement query with Union clause

Technical queries
, ,
#1

Hello Helical,

I am using Helical Insight 4.0. I want to implement the following query. How is it possible?

select a.* from (

SELECT Date(data), ID, AVG(column), COUNT(1), 'FIRST' FROM database.tablename where someColumn= 'W'

and TIME(time) Between ('00:00:00') and ('05:59:59')

GROUP BY DATE(DATA)

union

SELECT Date(data), ID, AVG(column), COUNT(1), ‘SECOND’ FROM database.tablename where someColumn= 'W'

and TIME(time) Between ('00:00:00') and ('05:59:59')

GROUP BY DATE(DATA)

UNION

SELECT Date(data), ID, AVG(column), COUNT(1), 'THIRD' FROM database.tablename where someColumn= 'W'

and TIME(time) Between ('00:00:00') and ('05:59:59')

GROUP BY DATE(DATA)

) a

order by 1, 5,2;

Thank You,
Snow.

#2

Hello Snow,

Since union queries are not directly possible to be generated using the Adhoc Reports interface, you can add the below query as a “View” at the metadata level.
Go to Metadata Create or Metadata Edit page. Go to the “Views” section.

image

Click on “Add” button and insert the query in the section that appears

image

Click on “Retrieve Columns”. Once the column names appear, give a name to the view and Save.
Once done, save the metadata and you will see the view in the list of tables. Use the columns from this view to create the report.

Thank You,
Helical Insight.