7

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?

M Azam
  • 518
  • 1
  • 7
  • 28
  • 1
    Like: `SELECT videoCount, SessionCount FROM (select count(*) AS VideoCount from video where monthname(views) = 'May') AS VideoStats CROSS JOIN (select sum(sessions) AS SessionCount from user where user_id = 6) AS UserStats` ? – scragar May 16 '14 at 17:09

2 Answers2

12

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

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • This does answer the question as presented with their specific queries, but I'll point out the first query in this answer only works for subqueries that produce a single column. Which is not answering the question "Combine results of two unrelated queries into single view" in a universal way. – Dan Feb 07 '21 at 14:00
8
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.

thebignoob
  • 471
  • 5
  • 11