HI EVERYONE.

GOOD DAY TO YOU ALL! i WOULD LIKE TO GET SOME OPINION SINCE I AM KINDA STUCK IN THIS QUERY I AM WORKING ON.

WE HAVE THIS EXISTING QUERY

SELECT
f.A_OBJ_ID,
f.B_OBJ_ID,
SUM(f.C) R_COUNT,
SUM(f.D) A_SPEND

FROM
FACT f JOIN
TIME t ON f.T_OBJ_ID = t.OBJ_ID
WHERE
t.TWELVE = 1
GROUP BY
f.F_OBJ_ID,
f.P_OBJ_ID
ORDER BY
f.F_OBJ_ID,
SUM(f.D) DESC;

NOW I HAVE TO IMPLEMENT A NEW FIELD IN THIS REPORT CALLED B_SPEND AS YOU CAN SEE, A_SPEND SHOULD BE BASED ON T.TWELVE = 1 (ACCORDING TO THE QUERY, WHERE T.TWELVE = TRUE) BUT THE NEW FIELD CONDITION HAS TO BASED ON A DIFFERENT FIELD BUT SAME TABLE WHERE T.WELVE RESIDES.

THE QUESTION IS, CAN I GET THE SUM OF A_SPEND BASED ON T.TWELVE = 1 AND SUM OF B_SPEND BASED T.THREE = 1 CONDITION?

I WANT TO GET THE SUM OF SPEND BASED ON LAST TWELVE MONTHS (T.TWELVE= 1) AND LAST 3 MONTHS (T.THREE =1)

IF I TRY TO DO THIS:

SELECT
f.A_OBJ_ID,
f.B_OBJ_ID,
SUM(f.C) R_COUNT,
SUM(f.D) A_SPEND,
SUM(F.D) B_SPEND

FROM
FACT f JOIN
TIME t ON f.T_OBJ_ID = t.OBJ_ID
WHERE
t.TWELVE = 1 AND t.THREE = 1
GROUP BY
f.F_OBJ_ID,
f.P_OBJ_ID
ORDER BY
f.F_OBJ_ID,
SUM(f.D) DESC;

IT GIVES ME THE SAME SPEND VALUE TO BOTH A_SPEND AND B_SPEND FIELDS which is the A_SPEND VALUE.

SHOULD I JUST CREATE A NEW TABLE FOR THE THREE MONTH VALUES? THIS IS AGAINST THE REQUIREMENT THOUGH
BUT IF SQL CANNOT DISPLAY 2 VALUES BASED ON 2 CONDITIONS UNDER THE SAME TABLE, THEN I GUESS I'LL JUST HAVE TO DO THAT.

THANKS A LOT FOR YOUR HELP!