CODEWITHSUNDEEP
sqlsql-server
dejanm
dejanm

Reputation: 133

Show Sum only if Sum > 0

I want to receive the MobileNr Amount for 01.05.2021.

This is my query with which I tried to group those accounts, is this the right way?

DECLARE @specified_date DATE;
SET @specified_date = '01.05.2021';

SELECT
    CustomerNr, MobileNr, 
    ROUND(SUM(CASE  
                  WHEN Bil_Start_Date = @specified_date 
                      THEN Price
                      ELSE 0 
              END), 2) AS Summ
FROM
    BillingTable  
WHERE
    MobileNr <> ''
GROUP BY
    CustomerNr, MobileNr

If I wrote everything well, how can I not show accounts where Summ > 0?

This is my SQL Server BillingTable with sample data:

CustomerNr Bil_Start_Date Price MobileNr
34776 01.05.2021 337.05 136550
34776 01.05.2021 72.69 136550
34776 01.05.2021 0.27 136550
34776 01.05.2021 9.6 136550
34776 01.05.2021 -409.74 136550
34776 01.05.2021 -0.27 136550
34776 01.05.2021 -9.6 136550
34776 01.05.2021 120.51 137496
34776 01.05.2021 34.53 137496
34776 01.05.2021 59.76 137496
34776 01.05.2021 0.12 137496
34776 01.05.2021 -120.51 137496
34776 01.05.2021 -59.85 137496

I would like the result to be as follows:

CustomerNr    MobileNr    Summ
---------------------------------
   34776      137496      34.56

Thank you all for your suggestions and help

Upvotes: 1

Views: 233

Answers (2)

BlackMath
BlackMath

Reputation: 1848

You need to use HAVING statement in this case:

HAVING sum(.....) > 0

This is the statement structure:

   SELECT
        select_list
    FROM
        table_name
    GROUP BY
        group_list
    HAVING
        conditions;

The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified list of conditions.

In this syntax, the GROUP BY clause summarizes the rows into groups and the HAVING clause applies one or more conditions to these groups.

Upvotes: 1

trillion
trillion

Reputation: 1401

ADJUST THIS :

ROUND(sum(CASE  
             WHEN Bil_Start_Date = @specified_date AND PRICE >= 0 THEN Price
              ELSE 0 )



GROUP BY 
       CustomerNr, MobileNr 
HAVING 
       ROUND(SUM(CASE WHEN Bil_Start_Date = @specified_date THEN Price ELSE 0 END), 2) > 0

Upvotes: 2

Related Questions