1

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?

4
  • SELECT (...)+(...) as count GROUP BY .... where is your FROM ??? – Luuk Mar 7 '20 at 20:58
  • @Luuk I don't think there's a place to write a from, I want something like this stackoverflow.com/a/826375/1920003 and I want the video title too, that's the difference between my query and the one in that link. If you remove video_title, the query above works perfectly, therefore I doubt that the problem is the lack of a from. If you have a different query in mind, please answer the question and if it works I'll accept your answer – Lynob Mar 7 '20 at 21:10
  • The column video_title has to come from soemwhere, usually you get it from a FROM clause. But as you didn't provide any example data for example in a dbfoddle. – nbk Mar 8 '20 at 0:51
  • @nbk now i understand the question I'm being asked. That comes from videos – Lynob Mar 8 '20 at 2:00
2

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 😉

3
  • 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 – Lynob Mar 8 '20 at 10:09
  • 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. – Luuk Mar 8 '20 at 10:23
  • Thank you so much. – Lynob Mar 8 '20 at 11:34
1

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
1
  • Something is wrong with the query, all the videos have the same count – Lynob Mar 8 '20 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.