MySQL: How select the count of two queries while selecting another field too?

Question

I'm trying to get how many orders happened during a month, if the order has a duration of 168, it's a weekly transaction so I have to divide it by 4, and get the ceiling. If an order has a duration of 720, it's a monthly transaction.

I want the result to contain the video_title and count, which is the transactions count which is all the monthly transactions + (weekly trasactions/4)

I tried the query below, it's correct but I get an error when trying to select the video title too which comes from videos table, how to select the video title as well?

SELECT video_title,
(
 SELECT CEILING(COUNT(*)/4) FROM videos
 INNER JOIN wtb_order_archives
 ON wtb_order_archives.object_name = videos.video_name
 WHERE wtb_order_archives.is_test=0
 and wtb_order_archives.bypass=0
 and videos.is_movie=1
 and videos.owner = 'user'
 and wtb_order_archives.is_paid=1
 and wtb_order_archives.duration =168
 and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
 ) 
 +
 (
 SELECT COUNT(*)
 FROM videos
 INNER JOIN wtb_order_archives
 ON wtb_order_archives.object_name = videos.video_name
 WHERE wtb_order_archives.is_test=0
 and wtb_order_archives.bypass=0
 and videos.is_movie=1
 and videos.owner = 'user'
 and wtb_order_archives.is_paid=1
 and wtb_order_archives.duration =720
 and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
 )
 as count
 GROUP BY video_title ORDER BY count ASC"
ORDER BY count ASC

And also what indexes are the best for such a query?

Answer 1

In your query you are adding the results (SELECT COUNT(*)....) from two queries together. There is no reference to a video_title in this count. I added this reference to the video_title which makes sure the count is done for every video_title.

The left join makes sure the every video is selected, and that's why IFNULL(..) is added. If the link to a subquery fails, the count is NULL, the function IFNULL replaces that with 0.

SELECT 
 v.video_title, 
 ISNULL(c1.count,0)+ISNULL(c2.count,0) as count
FROM videos v
LEFT JOIN
 (
 SELECT video_title,CEILING(COUNT(*)/4) as count FROM videos
 INNER JOIN wtb_order_archives
 ON wtb_order_archives.object_name = videos.video_name
 WHERE wtb_order_archives.is_test=0
 and wtb_order_archives.bypass=0
 and videos.is_movie=1
 and videos.owner = 'user'
 and wtb_order_archives.is_paid=1
 and wtb_order_archives.duration =168
 and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
 GROUP BY video_title
 ) c1 on c1.video_title = v.video_title
LEFT JOIN
 (
 SELECT video_title, COUNT(*) as count
 FROM videos
 INNER JOIN wtb_order_archives
 ON wtb_order_archives.object_name = videos.video_name
 WHERE wtb_order_archives.is_test=0
 and wtb_order_archives.bypass=0
 and videos.is_movie=1
 and videos.owner = 'user'
 and wtb_order_archives.is_paid=1
 and wtb_order_archives.duration =720
 and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
 GROUP BY video_title
 ) c2 on c2.video_title = v.video_title 
ORDER BY count ASC

Because no input data was given, this is only a guess on what the desired query should look like 😉

• Lynob: Thank you so much, I'd like to dismiss the null values altogether. I tried replacing the left join with the inner join, got nothing
• Luuk: The null values for c1, of for c2??, and NULL+1 evaluates still to NULL. If you do not want to see videos with a count of 0, you should add WHERE ISNULL(c1.count,0)+ISNULL(c2.count,0) >0.
• Lynob: Thank you so much.

Answer 2

You can try this, but as you don't give any example data, i can't test, if this gives you the correct result.

SELECT video_title,
(
 (SELECT CEILING(COUNT(*)/4) FROM videos
 INNER JOIN wtb_order_archives
 ON wtb_order_archives.object_name = videos.video_name
 WHERE wtb_order_archives.is_test=0
 and wtb_order_archives.bypass=0
 and videos.is_movie=1
 and videos.owner = 'user'
 and wtb_order_archives.is_paid=1
 and wtb_order_archives.duration =168
 and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
 ) 
 +
 (
 SELECT COUNT(*)
 FROM videos
 INNER JOIN wtb_order_archives
 ON wtb_order_archives.object_name = videos.video_name
 WHERE wtb_order_archives.is_test=0
 and wtb_order_archives.bypass=0
 and videos.is_movie=1
 and videos.owner = 'user'
 and wtb_order_archives.is_paid=1
 and wtb_order_archives.duration =720
 and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
 )
 )
 as count
FROM videos
GROUP BY video_title 
ORDER BY count ASC

• Lynob: Something is wrong with the query, all the videos have the same count