1

I have this SQL statement:

select a.title, a.video_id, b.id
from
    tzrat_community_videos a
    inner join
    tzrat_video_views b on a.video_id = b.videoid
where
    b.cdate >= '2014-01-01 00:00:00' and
    b.cdate <= '2014-06-26 23:59:59' and
    a.video_id = 'dCflt1d2xPw' and
    a.published = 1 and b.duration != 0 and
    (
        (a.percent is not null and (100*b.seconds)/b.duration >= a.percent ) or
        ((100*b.seconds)/b.duration >= 80)
    )
    group by b.videoid, a.title, a.video_id, b.id

Result: https://i.sstatic.net/I649X.png

But I want to have only the title, video_id and the number of times it appears (I want the number of views this video have) So , I make this statement:

select a.title, a.video_id, count(b.id) as views
from
    tzrat_community_videos a
    inner join
    tzrat_video_views b on a.video_id = b.videoid
where
    b.cdate >= '2014-01-01 00:00:00' and
    b.cdate <= '2014-06-26 23:59:59' and
    a.video_id = 'dCflt1d2xPw' and
    a.published = 1 and
    b.duration != 0 and
    (
        (a.percent is not null and (100*b.seconds)/b.duration >= a.percent ) or
        ((100*b.seconds)/b.duration >= 80)
    )
group by b.videoid, a.title, a.video_id

But this returns me this : https://i.sstatic.net/fD1zY.png

So... what am I doing wrong? is the same statement, just b.id -> count(b.id) as views

0

1 Answer 1

3

In the first case, you group also by b.Id

What you don't do in the second case (of course, as it's in the aggregate function).

So change

count(b.id)

by

count(distinct b.id)

if you want to avoid duplicated b.id count.

Sign up to request clarification or add additional context in comments.

1 Comment

@user3770536 no prob. Don't forget to accept the answer to "close this case" ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.