[RESOLVED] SQL query

[Serigraphie]

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!

[si_the_geek]

Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB.Net, it is certainly not specific to VB.Net)


By the way, your SHOUTING makes your post very hard to read.

[smendoza]

If you're using MSSQL you could use a CTE instead it would look clearing imo. I didn't test this query but it should could use it to return both values (sum of 12month and sum of 3 months)

SELECT *
FROM (
        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 
        GROUP BY f.F_OBJ_ID, f.P_OBJ_ID
      ) as twelveMonths 
INNER JOIN 
     (      
       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.THREE = 1
       GROUP BY f.F_OBJ_ID, f.P_OBJ_ID
      ) as threeMonths
  ON  twelveMonths.T_OBJ_ID = threeMonths.T_OBJ_ID