Combine results of two unrelated queries into single view

mysql sql sql-view
M Azam picture M Azam · May 16, 2014 · Viewed 16.5k times · Source

Is it possible to combine the results of two separate (unrelated) sql queries into a single view. I am trying to total some figures for users and count the views for videos this month to display on a dashboard.

i.e.,

select count(*) from video where monthname(views) = 'May';

and

select sum(sessions) from user where user_id = 6;

I would like to create a view that combines that contains these two results.

Is this possible?

Answer

Hart CO picture Hart CO · May 16, 2014

If you want the results next to each other in separate columns you can simply SELECT a list of queries:

SELECT ( select count(*) from video where monthname(views) = 'May') AS May_CT
      ,( select sum(sessions) from user where user_id = 6) AS User_Sum

If you want the results stacked in one column:

select count(*) from video where monthname(views) = 'May'
UNION  ALL
select sum(sessions) from user where user_id = 6

The latter may require datatype conversion

Related questions

How do MySQL views work?

When I create a view I am basically making a new table that will automatically be transacted upon when data in one of the tables it joins changes; is that correct? Also why can't I use subqueries in my view?

Read More
MySQL - create view using subquery in FROM clause

I want to create view in MySQL using a subquery CREATE OR REPLACE VIEW `V_TASK_TRANSFER` (`REF_ID`, `DATE_CREATE`, `DATE_TRX`, `ACCOUNT_NO`, `TO_ACCOUNT_NO`, `TO_NAME`, `CURRENCY_CODE`, `AMOUNT`, `TASK_TYPE`, `NAME_E`, `NAME_I`, `REF_…

Read More
MySQL Views: Referencing one calculated field (by name) in another calculated field

How can I define a view that has two calculated fields, for instance... ('TableName'.'BlueSquares' + 'TableName'.'RedSquares') AS TotalSquares, ('TableName'.'BlueCirles' + 'TableName'.'RedCircles') AS TotalCircles ... and create a third calculated field that's based on the first two calculated fields, as …

Read More