SQL SUM using CASE WHEN within two tables

RobeLTDP 0
Viewed 42

I'm sure it has to easier than what I'm doing, but I'm struggling to get this done. I need to get the expenses made for an ID on table A, but in case that ID exists on table B, the total expenses are the sum of all the rows with that ID on that table.

This is the query I'm using:

SELECT gastos.id,
       gastos.importe,
       SUM(asignacion_gastos.importe) AS "totalAsignado",
       CASE
         WHEN "totalAsignado" IS NULL THEN
          "totalImporte" = gastos.importe
         ELSE
          "totalImporte" = "totalAsignado"
       END
  FROM gastos
  LEFT JOIN asignacion_gastos
    ON gastos.id = asignacion_gastos.idGasto
 GROUP by gastos.id
 ORDER BY gastos.id

I'm inserting this data in a Datatable, so, even if the the easiest solution would be to check it in the server side processing via PHP, I'd rather doing it in the query to run the app faster. (If I'm not wrong db queries are faster than PHP processing through thousands of results).

mysql sql
1 Answers

If you could post scripts of table creation and data insertion, it would help a lot. Any way I assumed that gastos is table A and asignacion_gastos is table B. You can join these tables and because you wanna group by gastos.id you should use an aggregate function to check other columns.

SELECT CASE
         WHEN MIN(asignacion_gastos.idGasto) IS NOT NULL THEN
          SUM(asignacion_gastos.importe)
         ELSE
          MAX(totalAsignado)
       END AS Total
  FROM gastos
  LEFT JOIN asignacion_gastos
    ON gastos.id = asignacion_gastos.idGasto
 GROUP BY gastos.id
userhAAv0
Related