Mysql - Add values from a column and also bring value from each row

Asked

Viewed 48 times

0

I have a certain column and need to return both the sum of the values of the same, as well as the individual values of each row.

I did a test with UNION and even worked, the first line returned to SOMA and the other individual values of each line. But I don’t know if this is right and if there’s another way to do it. I did so, that’s right?

SELECT SUM(val_resgate) AS resgate FROM ETAPA WHERE fk_servico=39 
UNION
SELECT val_resgate FROM ETAPA WHERE fk_servico=39

2 answers

2

Yes is an acceptable way to do it but in the case of Mysql I recommend using UNION ALL and not UNION because its default is to use the UNION DISTINCT. It is also worth remembering that the behavior with UNION ORDER BY differs if it is grouped with SELECT or at the end of UNION where it sorts the whole result.

Using your example:

This makes it put the total at the top and sort the other rising values

(SELECT SUM(val_resgate) AS resgate FROM ETAPA WHERE fk_servico=39)
UNION ALL
(SELECT val_resgate FROM ETAPA WHERE fk_servico=39 ORDER BY val_resgate)

This way already Toral will be the last line of the result

(SELECT SUM(val_resgate) AS resgate FROM ETAPA WHERE fk_servico=39)
UNION ALL
(SELECT val_resgate FROM ETAPA WHERE fk_servico=39)
ORDER BY val_resgate

0

I think you can use "group by" with "with rollup" to get that sum at the end. Ex:

SELECT SUM(val_resgate) AS resgate 
FROM ETAPA 
WHERE fk_servico=39
GROUP BY id_etapa WITH ROLLUP

I hope it helps

Browser other questions tagged

You are not signed in. Login or sign up in order to post.