Doubt in preparing a query in SQL

Question

Doubt in preparing a query in SQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

Could you tell me what's wrong with this SQL code? I want for each movie, the salaries of each staff does not appear repeated, ie if the first movie has 2 leads , in the table I want to sum these values to the lead .

select Ms.salary as Total_salaries, R.title as papeis, M.title as filmes
from Movies M, Staff St, MovieStaff Ms, Roles R
where Ms.mvid=M.mvid
and Ms.stid=St.stid
and Ms.rid=R.rid
union 
select Ma.salary as Total_salaries, Ar.title as papeis_atores, M.title as filmes
from Movies M, Staff St, MovieActors Ma, ActorRoles Ar, Actors A
where Ma.mvid=M.mvid
and Ma.aid=A.aid
and A.aid=St.stid
and Ma.arid=Ar.arid;

So that this does not happen:

mysql union

asked by anonymous 17.12.2016 / 18:41

2 answers

Understanding Automatic Payback Plugin to post content to Facebook as posted on the site

score 0 · Answer 1

In this case, you must use a sum(coluna) aggregation function that sums the values of the column and the group by that groups the data by the columns that are selected except the one that is doing the calculation.

select sum(Ms.salary) as Total_salaries, R.title as papeis, M.title as filmes
from Movies M, Staff St, MovieStaff Ms, Roles R
where Ms.mvid=M.mvid
and Ms.stid=St.stid
and Ms.rid=R.rid
group by papeis, filmes
union 
select sum(Ma.salary) as Total_salaries, Ar.title as papeis_atores, M.title as filmes
from Movies M, Staff St, MovieActors Ma, ActorRoles Ar, Actors A
where Ma.mvid=M.mvid
and Ma.aid=A.aid
and A.aid=St.stid
and Ma.arid=Ar.arid
group by papeis, filmes;

I answered your question, but it is good to study about the group by: link

score 0 · Answer 2

0

You can add wages, but you can not add the movies and roles. You can see this in the link that Laércio Lopes sent ...

If you need some code, send the sql file with the tables used in the example.

12.01.2017 / 21:33