hello all i am trying to get two results with the same column in sql using case.
SELECT de.MONTH,
SUM(CASE WHEN cf.NOM_CF = 'COST' THEN de.MB END) AS out,
SUM(CASE WHEN cf.NOM_CF = 'ENTRY' THEN de.MB END) AS in
FROM DETALLE_ARCHIVO_IOT de
INNER JOIN ARCHIVO_IOT cab ON cab.ID_ARCHIVO_IOT = de.ID_ARCHIVO_IOT
INNER JOIN PLMN p ON p.CODE_PLMN = de.code
INNER JOIN CF cf ON cf.ID_CF = de.ID_CF
WHERE de.MONTH BETWEEN '202101' AND '202104' AND de.CALL_TYPE = 'GPRS' AND cab.ESTADO_DETALLE = 1
GROUP BY cf.NOM_CF, de.MONTH
ORDER BY de.MONTH;
but i get this error:
- 00000 – "FROM keyword not found where expected"
what am i doing wrong??
expected result:
+--------+------+-----+
|MONTH |IN |OUT |
+--------+------+-----+
|202101 | 21 | 12 |
|202102 | 31 | 13 |
+--------+------+-----+
>Solution :
IN is a reserved word; if you want to use it as an identifier then it needs to be quoted:
SELECT de.MONTH,
SUM(CASE WHEN cf.NOM_CF = 'COST' THEN de.MB END) AS OUT,
SUM(CASE WHEN cf.NOM_CF = 'ENTRY' THEN de.MB END) AS "IN"
FROM DETALLE_ARCHIVO_IOT de
INNER JOIN ARCHIVO_IOT cab ON cab.ID_ARCHIVO_IOT = de.ID_ARCHIVO_IOT
INNER JOIN PLMN p ON p.CODE_PLMN = de.code
INNER JOIN CF cf ON cf.ID_CF = de.ID_CF
WHERE de.MONTH BETWEEN '202101' AND '202104' AND de.CALL_TYPE = 'GPRS' AND cab.ESTADO_DETALLE = 1
GROUP BY cf.NOM_CF, de.MONTH
ORDER BY de.MONTH;