Multiple Select Sum with related table parameters

Asked

Viewed 48 times

-1

I tried to make a Subselect query when a user wants to search the Asset by Product, by Location and by Situation the Total of the sum is changed. And it only adds the Asset to that = true. Follow the Sql command I did. Please ask for help.

SELECT USUARIO.[usu_codigo], Sum(IIF(PATRIMONIO.[pat_ativo] = true, (PATRIMONIO.[pat_valortotal]), 0)) As Soma,  
    (SELECT Sum(IIF(PATRIMONIO.[pat_ativo] = true, (PATRIMONIO.[pat_valortotal]), 0)) WHERE PATRIMONIO.[pat_codproduto] = PRODUTO.[prod_codigo] And ((PRODUTO.[prod_nome]) = ?)) As SomaProduto, 
    (SELECT Sum(IIF(PATRIMONIO.[pat_ativo] = true, (PATRIMONIO.[pat_valortotal]), 0)) WHERE PATRIMONIO.[pat_codlocalizacao] = LOCALIZACAO.[loc_codigo] And ((LOCALIZACAO.[loc_nome]) = ?)) As SomaLocalizacao,  
    (SELECT Sum(IIF(PATRIMONIO.[pat_ativo] = true, (PATRIMONIO.[pat_valortotal]), 0)) WHERE PATRIMONIO.[pat_codlocalizacao] = PATRIMONIO.[pat_situacao] And ((PATRIMONIO.[pat_situacao]) = ?)) As SomaSituacao  
FROM USUARIO, PATRIMONIO, PRODUTO, LOCALIZACAO 
GROUP BY USUARIO.[usu_codigo], PRODUTO.[prod_nome], LOCALIZACAO.[loc_nome], PATRIMONIO.[pat_situacao] 
HAVING(((USUARIO.[usu_codigo]) = ?));
c# ms-access

1 answer

0

It worked that way, I don’t know how it will look in terms of performance. Anyone has any suggestions to simplify this select?

SELECT DISTINCT * FROM (SELECT USUARIO. [usu_codigo], Sum(IIF(PATRIMONIO. [pat_ativo] = true, (PATRIMONIO. [pat_valortotal]), 0) FROM USUARIO LEFT JOIN PATRIMONIO ON USUARIO. [usu_codigo] = PATRIMONIO. [pat_codusuario] GROUP BY USUARIO. [usu_codigo] HAVING(((USUARIO.[usu_codigo]) = ?)) UNION ALL SELECT PRODUCT. [prod_nome], Sum(IIF(PATRIMONIO.[pat_ativo] = true, (PATRIMONIO.[pat_valortotal]), 0) FROM PRODUCT LEFT JOIN PATRIMONIO ON PRODUCT. [prod_codigo] = PATRIMONIO. [pat_codproduto] GROUP BY PRODUTO. [prod_nome] HAVING(((PRODUTO.[prod_nome]) = ?)) UNION ALL SELECT LOCATION. [loc_codigo], Sum(IIF(PATRIMONIO.[pat_ativo] = true, (PATRIMONIO.[pat_valortotal]), 0) FROM LOCATION LEFT JOIN PATRIMONIO ON LOCATION. [loc_codigo] = PATRIMONIO. [pat_codlocalizacao] GROUP BY LOCALIZACAO. [loc_codigo] HAVING(((LOCALIZATION.[loc_codigo]) = ?)) UNION ALL SELECT PATRIMONIO. [pat_situacao], Sum(IIF(PATRIMONIO.[pat_ativo] = true, (PATRIMONIO.[pat_valortotal]), 0) FROM PATRIMONIO GROUP BY PATRIMONIO. [pat_situacao] HAVING(((PATRIMONIO. [pat_situacao]) = ?)));

Browser other questions tagged c# ms-access

You are not signed in. Login or sign up in order to post.