Trouble With Query in SQL

Question

I have the following inconvenience with SQL.
I have to list the contents reproduced more than 3 times in each month of 2018 and less than 2 in the months that go of the 2019.

The structure for that is the following:

CREATE TABLE content (
    content INT NOT NULL,
    CONSTRAINT PK_Codcontent PRIMARY KEY(Codcontent) 
)

CREATE TABLE Reproduced (
    Coduser INT NOT NULL,
    Codcontent INT NOT NULL,
    CONSTRAINT PK_Coduser PRIMARY KEY(Coduser, Codcontent),
    CONSTRAINT FK_Codusr FOREIGN KEY(Coduser) REFERENCES Perfil(Codpuser),
    CONSTRAINT FK_Codcont FOREIGN KEY(Codcontent) REFERENCES Contenido(Codcontent)
)

I have created the following view to list the contents, reproductions and months of the whole year, but the thing is that I do not know how to filter those with more than 3 reproductions for each month.

CREATE VIEW TODOSLOSMESES2018
AS
SELECT
      R.Codcontenido
     , count(*)Reproducciones
     , count(Distinct Month(R.fecha)) as 'Meses'
FROM (
       SELECT * 
       FROM Reproduce 
       WHERE fecha>='20180101' AND fecha<='20181231')R
group by Codcontenido
HAVING count(Distinct Month(R.fecha))=12

You would need to add the Month in the Group by and change the Having to Count(*) Reproducciones > 3 — SS_DBA
– SS_DBA, Commented Jun 4, 2019 at 12:49
so you only want a list of reproduced's when all 12 months have more than 3 in them or just 3? I would suggest breaking it down into steps. First get a list of reproductions wtih their counts by month. Then figure out how to ensure all 12 months have at least 3. — xQbert
– xQbert, Commented Jun 4, 2019 at 12:50

Gordon Linoff · Accepted Answer · 2019-06-04 12:55:42Z

1

You can use conditional aggregation -- but two levels. One by content and month and the other by content:

select codcontent
from (select codcontent, year(fecha) as yr, month(fecha) as mon, count(*) as cnt
      from reproduced r
      group by codcentent, year(fecha), month(fecha)
     ) r
group by codcontent
having sum(case when year = 2018 then 1 else 0 end) = 12 and  -- all months
       min(case when year = 2018 then cnt end) >= 3 and
       max(case when year = 2019 then cnt else 0 end) < 3;

answered Jun 4, 2019 at 12:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Trouble With Query in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related