How do I make this query possible?-CodePudding

I've spent hours trying to figure out several ways to do this query and I can never do it.

This is the query that works but doesn't fully represent what I'm trying to achieve:

SELECT filme_id,(SELECT count(bilhetes.sessao_id) FROM bilhetes
WHERE bilhetes.sessao_id = sessoes.id GROUP BY bilhetes.sessao_id) as soma 
FROM sessoes

And this is the output:

[![enter image description here][1]][1]

As you can see there are several repeated 'filme_id'.

My goal is to somehow group all those 'filme_id' and SUM the 'soma' collumn with those with the same 'filme_id'.

I've spent a lot of time around this and couldn't figure it out so I'm reaching out for help.

If you have any idea how to get the output I'm looking for in SQL is enough, but if you see there's a way to do it much easier in Laravel, go ahead.

For those interested in the Laravel query here it is:

$ocupacaoPorSessao = DB::table('sessoes')
    ->select('bilhetes.sessao_id','sessoes.filme_id','sessoes.sala_id',DB::raw("COUNT(bilhetes.sessao_id) as count"))
    ->join('bilhetes','bilhetes.sessao_id','=','sessoes.id')
    ->groupBy('bilhetes.sessao_id')
    ->orderBy('sessoes.filme_id')
    ->get();

My goal is to Sum the attendance of every movie session for every single movie so I can have the total attendance for every movie.

Table 'bilhetes' has all the tickets ever purchased, having its own PK(id) and a FK(sessao_id) that points to Table 'sessoes' that has all the movie Sessions ever created, having its PK(id) and a FK(filme_id) that points to a table with all the movies [1]: https://i.stack.imgur.com/LXjZ3.png

CodePudding user response：

You can try this:

SELECT sessoes.filme_id,count(bilhetes.sessao_id) as soma FROM sessoes JOIN bilhetes ON bilhetes.sessao_id = sessoes.id GROUP BY bilhetes.sessao_id

CodePudding user response：

Will this query work?

SELECT filme_id, SUM(soma) AS Result
FROM 
 ( SELECT filme_id,
   ( SELECT count(bilhetes.sessao_id) 
     FROM bilhetes
     WHERE bilhetes.sessao_id = sessoes.id 
     GROUP BY bilhetes.sessao_id
   ) as soma 
     FROM sessoes
 )
GROUP BY filme_id