CODEWITHSUNDEEP
sql-serverselectnullsumhaving
Schizophrenia
Schizophrenia

Reputation: 47

How to list result in null or empty data from SQL Server having sum?

How to list products that are not (null = 0) ?

STHAR TABLE

id        STHAR_GCKOD        STHAR_GCMIK
------------------------------------------------
1             NULL               NULL
99             G                  2
99             C                  2
1525           G                  3
1525           C                  2
------------------------------------------------

NOTE: G = (+)  /  C = (-)

RESULT

ID 1;     Not found in result, i want to found in result (TOTAL : 0)
ID 99;    TOTAL : 0
ID 1525;  TOTAL : 1

My code:

SELECT 
   B.STOK_KODU, B.STOK_ADI, B.URETICI_KODU, B.KOD_1,
   SUM(CASE A.STHAR_GCKOD 
         WHEN 'G' THEN STHAR_GCMIK 
         ELSE - STHAR_GCMIK 
       END) BAKIYE
FROM 
    LSTHAR A
RIGHT OUTER JOIN 
    TBLSTSABIT B ON B.STOK_KODU=A.STOK_KODU
GROUP BY 
    B.STOK_KODU, B.STOK_ADI, B.URETICI_KODU, B.KOD_1
HAVING
    SUM(CASE A.STHAR_GCKOD WHEN 'G' THEN STHAR_GCMIK 
                           ELSE - STHAR_GCMIK END) < B.KOD_6
ORDER BY 
    B.STOK_KODU ASC

Can not find the list of results is null or empty data,

Let the list of results in the fact that I would like

possible ?

Thank you ..

Upvotes: 0

Views: 444

Answers (1)

EricZ
EricZ

Reputation: 6205

Try this

SELECT 
   B.STOK_KODU, B.STOK_ADI, B.URETICI_KODU, B.KOD_1,
   SUM(CASE A.STHAR_GCKOD 
         WHEN 'G' THEN STHAR_GCMIK 
         ELSE ISNULL(- STHAR_GCMIK,0) END) 
       END) BAKIYE
FROM 
    LSTHAR A
RIGHT OUTER JOIN 
    TBLSTSABIT B ON B.STOK_KODU=A.STOK_KODU
GROUP BY 
    B.STOK_KODU, B.STOK_ADI, B.URETICI_KODU, B.KOD_1
HAVING
    SUM(CASE A.STHAR_GCKOD WHEN 'G' THEN STHAR_GCMIK 
                           ELSE ISNULL(- STHAR_GCMIK,0) END) < B.KOD_6
ORDER BY 
    B.STOK_KODU ASC

EDIT: Not sure why you got error message. However, the problem of not getting NULL value in your original query is the CASE statument below

CASE A.STHAR_GCKOD 
         WHEN 'G' THEN STHAR_GCMIK 
         ELSE - STHAR_GCMIK 
       END

You can also try this, which more clear

CASE WHEN A.STHAR_GCKOD = 'G' THEN  STHAR_GCMIK 
     WHEN A.STHAR_GCKOD = 'C' THEN - STHAR_GCMIK 
     WHEN A.STHAR_GCKOD IS NULL THEN 0
END

Upvotes: 1

Related Questions