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 😉

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