Does anyone have an idea to improve this SQL??? 1

[Thiesen]

I write this SQL, but i think it's very ugly (but very functional)....

it make a sum of 3 diferents fields of BD and show then.

SELECT user_id, month, year, Sum(A808_real) AS T808_real, Sum(Airrf_real) AS Tirrf_real, Sum(sal_liq) as salario from

    (SELECT GG_OSS_captura_real.user_id, GG_OSS_captura_real.month, GG_OSS_captura_real.year, Sum(GG_OSS_captura_real.valor_real) AS A808_real, "0" as Airrf_real, "0" as sal_liq
    FROM GG_OSS_captura_real
    WHERE GG_OSS_captura_real.type_id = "00808"
    GROUP BY GG_OSS_captura_real.user_id, GG_OSS_captura_real.month, GG_OSS_captura_real.year

    UNION all

    SELECT GG_OSS_captura_real.user_id, GG_OSS_captura_real.month, GG_OSS_captura_real.year, "0" as A808_real, Sum(GG_OSS_captura_real.valor_real) AS Airrf_real, "0" as sal_liq
    FROM GG_OSS_captura_real
    WHERE GG_OSS_captura_real.type_id = "99001"
    GROUP BY GG_OSS_captura_real.user_id, GG_OSS_captura_real.month, GG_OSS_captura_real.year

    union all

    SELECT GG_OSS_captura_real.user_id, GG_OSS_captura_real.month, GG_OSS_captura_real.year, "0" as A808_real, "0" as Airrf_real, Sum(GG_OSS_captura_real.valor_real) AS sal_liq
    FROM GG_OSS_captura_real
    GROUP BY GG_OSS_captura_real.user_id, GG_OSS_captura_real.month, GG_OSS_captura_real.year)

    group by GG_OSS_captura_real.user_id, GG_OSS_captura_real.month, GG_OSS_captura_real.year

Anyone have a idea to make it more "beautifull"????

 

[PHV]

What about this ?

SELECT user_id, [month], [year]
, Sum(IIf(type_id="00808",valor_real,0)) AS T808_real
, Sum(IIf(type_id="99001",valor_real,0)) AS Tirrf_real
, Sum(valor_real) AS salario
FROM GG_OSS_captura_real
GROUP BY user_id, [month], [year]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Thiesen]

Thanks, PHV, but i am confused because don't understand why your SQL returns 43952 lines and my SQL return 43951 lines..

 

[PHV]

Use the unmatched query wizard to discover which row is missing in your query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Thiesen]

Bingo !!!! the diference is only because i'm forgot to put in the first SQL one modification that i do after....

, Sum(IIf(RD="D",valor_real,0)) AS salario

that's works GREAT now!!!

One Coffee to me and one Star to you...