CODEWITHSUNDEEP
sqlms-access
Francesco Esposto
Francesco Esposto

Reputation: 71

How can I group by a field by a condition of that specific field?

My table has this values:

SommaDiImporto | codice_ordine_data
828,13         | 003644260517,003573190517,003740260517,003693230517
804,73         | 003644260517,003573190517,003740260517,003693230517
357,86         | 003644260517,003573190517,003740260517,003693230517
1727,82        | 003644260517,003573190517,003740260517,003693230517
200,69         | 003644260520,003573190520,003740260520,003693230520
0              | 003644260520,003573190520,003740260520,003693230520
600,58         | 003644260520,003573190520,003740260520,003693230520
123,47         | 003644260520,003573190520,003740260520,003693230520

The second field has the first four rows with the same values and the last four rows with same values too. The query that I realized should return me this records:

SommaDiImporto | codice_ordine_data
3718,54        | 003644260517,003573190517,003740260517,003693230517
924,74         | 003644260520,003573190520,003740260520,003693230520

And this is what I expect:

SommaDiImporto | codice_ordine_data
3718,54        | 003644260517,003573190517,003740260517,003693230517
0              | 003644260520,003573190520,003740260520,003693230520

The first field of the last row should return 0 because in that grouped list there is at least a record setted to 0.

This is the query I realized:

SELECT SUM(IIF(TIMBRATURE.importo = 0, 0, TIMBRATURE.importo)) AS SommaDiImporto, TIMBRATURE.codice_ordine_data
FROM TIMBRATURE
GROUP BY TIMBRATURE.codice_ordine_data;

How I have to modify my query to return the desired result?

Many thanks!

Upvotes: 0

Views: 77

Answers (1)

Sanjay Rathod
Sanjay Rathod

Reputation: 317

For values without NULL:

SELECT IIF(MIN(TIMBRATURE.importo) = 0, 0, SUM(TIMBRATURE.importo)) AS SommaDiImporto, TIMBRATURE.codice_ordine_data
FROM TIMBRATURE
GROUP BY TIMBRATURE.codice_ordine_data;

For values with NULL:

SELECT IIF(MIN(ISNULL(TIMBRATURE.importo, 0)) = 0, 0, SUM(TIMBRATURE.importo)) AS SommaDiImporto, TIMBRATURE.codice_ordine_data
FROM TIMBRATURE
GROUP BY TIMBRATURE.codice_ordine_data;

Try if this works!

Note: The function ISNULL depends on the database. This article may help to find the exact function that should be used.

Upvotes: 1

Related Questions