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?
Best Answer
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 isNULL
, the functionIFNULL
replaces that with 0.Because no input data was given, this is only a
guess
on what the desired query should look like ?