CODEWITHSUNDEEP
mysqlsqlsql-view
M Azam
M Azam

Reputation: 518

Combine results of two unrelated queries into single view

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?

Upvotes: 7

Views: 17807

Answers (2)

thebignoob
thebignoob

Reputation: 471

SELECT t2.total_session,
       t1.watch_count
FROM
  (SELECT 1 AS common_key,
          count(*) AS watch_count
   FROM video
   WHERE monthname(views) = 'May') AS t1
JOIN
  (SELECT 1 AS common_key,
               sum(sessions) AS total_session
   FROM USER
   WHERE user_id = 6) AS t2 ON t1.common_key = t2.common_key;

Ofcourse, this will be very efficient only when the output in both t1 and t2 is one row.

Upvotes: 8

Hart CO
Hart CO

Reputation: 34774

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

Upvotes: 12

Related Questions