CODEWITHSUNDEEP
sql
KMR
KMR

Reputation: 49

How to get all rows when still using a IF NOT NULL

I have searched everywhere I could and can not find the solution to this (IF there is one). Also, I could be looking at this wrong (I am new into SQL).

I am trying to get the sum of gross sales where there is a cancellation date displayed. This code works just fine. My problem is: is there a way to still get all rows (i.e. null rows) to display as 0 gross sales? Am I looking at this completely wrong? Can someone help lead me in the right direction?

SELECT "Div Code", "Agent Name", SUM("Gross Sale")
FROM salessummary
WHERE "Cancel Date" IS NOT NULL
GROUP BY "Div Code", "Agent Name";

This data output is 12 rows showing: 

Div Code |  Agent Name  |  Gross Sale
  SC         John Smith      $120
  NC         Jane Smith      $130
  NC         Will Smith      $40

I want it to show all 30 rows (even if an agent has no cancellations): 

Div Code |  Agent Name  |  Gross Sale  
  SC         John Smith      $120  
  *SC         Jada Smith      $0*  
  NC         Jane Smith      $130  
  NC         Will Smith      $40

The problem is that my sum of gross sales is based on if there is a cancellation date. This is what defines my data.

Upvotes: 1

Views: 108

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If the issue is that Gross Sale could have NULL values, I would recommend:

SELECT "Div Code", "Agent Name", COALESCE(SUM("Gross Sale"), 0) as "Gross Sale"
FROM salessummary
WHERE "Cancel Date" IS NOT NULL
GROUP BY "Div Code", "Agent Name";

COALESCE() is the ANSI standard function that replaces a NULL values with the specified value. Doing it after the SUM() means it is only called once -- a micro-optimization, but NULL values are ignored in the SUM().

If you want all "div code"/"agent name" combos -- even those with no cancels -- use conditional aggregation:

SELECT "Div Code", "Agent Name",
        SUM(CASE WHEN "Cancel Date" IS NOT NULL THEN "Gross Sale" ELSE 0 END) as "Gross Sale"
FROM salessummary
GROUP BY "Div Code", "Agent Name";

Upvotes: 2

Valerica
Valerica

Reputation: 1630

You could try:

 SUM(ISNULL("Gross Sale",0))

Upvotes: 0

Related Questions