Need sql query

Question

table shows: showid, tutle, link show status runtime classification.
table episdes: episodeid, showid, episodeNumber,seasonNumber,airDate,title.

Hello, i need sql query to select title serial and count episodes which already released(edisodes.airDate - date release episode) please help me) thanks who help.

Andomar · Accepted Answer · 2012-08-20 12:51:45Z

2

select  s.title
,       s.serial
,       e.title
,       sum(case when e.airDate <= current_timestamp then 1 end) 
            over (partition by s.title, s.serial)
from    shows s
left join
        episodes e
on      e.showid = s.showid
        and e.airDate <= current_timestamp

edited Aug 20, 2012 at 12:51

answered Aug 20, 2012 at 12:38

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

2 Comments

Stars Over a year ago

its display only one show and total for all released episodes. i need a query which select all shows in table shows and total released episodes like e.showid = s.showid

Andomar Over a year ago

Ok, changed to return all shows, and the number of released shows using a window function

udalmik · Accepted Answer · 2012-08-20 13:23:59Z

1

Sub query could be used there. E.g.

select s.title, s.serial, e.count
from shows s, (select showId, count(*) as count 
                from episodes 
                where airDate <= current_timestamp
                group by showId) e
where s.showId == e.showId

answered Aug 20, 2012 at 13:23

udalmik

8,02829 silver badges42 bronze badges

3 Comments

Andomar Over a year ago

This would return one row per show? Didn't you comment that you're looking for all episodes per show?

Stars Over a year ago

Its return all show and count released episodes current show.

Andomar Over a year ago

If that's what you're after, consider accepting the answer (the green V below the vote buttons, to the left of the answer)

Collectives™ on Stack Overflow

Need sql query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related