Get sum() result in one query in Mysql

Question

Hello, I have a table

id name state num
1  x    in    10
2  x    out   1
3  x    in    5
4  x    out   2
5  y    ...
6  y    ...

The expected result is to sum the num for 'in' and 'out' in one query and then calculate the difference, like

name numin numout diff
x    15    3      12
y    ....

Thanks

Answer 1

What you want is GROUP BY (and the CASE statement):

SELECT
  name,
  SUM(CASE WHEN state='in' THEN num ELSE 0 END CASE) AS numin,
  SUM(CASE WHEN state='out' THEN num ELSE 0 END CASE) AS numout,
  SUM(CASE WHEN state='in' THEN num ELSE -1*num END CASE) AS diff
FROM
  table
GROUP BY
  name