How to join two tables getting null entries for rows not matching a certain where clause and providing the sum if the clause matches

Question

I have the following two tables in a Firebird 2.5 database:

1. BonPos (named P below) with these relevant columns: KASSENABSCHLUSS_NR, BON_ID, GV_TYP
2. BonPos_Ust (named PU below) with these relevant columns: Z_KASSE_ID, KASSENABSCHLUSS_NR, BON_ID, POS_BRUTTO

The select shall produce:

1. The sum of PU.POS_BRUTTO for those entries where P.GV_TYP = 14
2. 0/Null for all entries where P.GV_TYP is not 14

My current select:

select pu.KASSENABSCHLUSS_NR, SUM(pu.POS_BRUTTO)
from BONPOS_UST pu, BONPOS p
where (pu.Z_KASSE_ID = 'MeineKasse') and
      (pu.KASSENABSCHLUSS_NR = p.KASSENABSCHLUSS_NR) and
      (pu.BON_ID = p.BON_ID) and
      (p.GV_TYP = 14)
group by pu.KASSENABSCHLUSS_NR
order by pu.KASSENABSCHLUSS_NR

This produces a list of all brutto sums of all receipts which have a GV_TYP of 14. But the way group by works for null values it combines them all in one row.

Any way to avoid this?

Update: tried this join meanwhile:

select p.KASSENABSCHLUSS_NR, SUM(pu.POS_BRUTTO)
from KASSE_BONPOS p
left join KASSE_BONPOS_UST pu
on (pu.Z_KASSE_ID = 'MeineKasse') and
   (pu.KASSENABSCHLUSS_NR = p.KASSENABSCHLUSS_NR) and
   (pu.BON_ID = p.BON_ID)
WHERE (p.GV_TYP = 14)
group by p.KASSENABSCHLUSS_NR
order by p.KASSENABSCHLUSS_NR 

But this only produces a list of all KASSENABSCHLUSS_NR where the sum is Null. I'd need the entries with GV_TYP = 14 and a sum > 0 in between.

Here's some expected sample output:

<table border="1">
  <tr><td>KASSENABSCHLUSS_NR</td><td>SUM</td></tr>
  <tr><td>0</td><td>3.000</td></tr>
  <tr><td>1</td><td>Null</td></tr>
  <tr><td>2</td><td>Null</td></tr>
  <tr><td>3</td><td>-5.000</td></tr>
  <tr><td>4</td><td>2.500</td></tr>
</table>

CREATE TABLE BONPOS (
BON_ID Integer NOT NULL,
POS_ZEILE Integer NOT NULL,
Z_KASSE_ID Varchar(30),
GV_TYP Integer NOT NULL,
KASSENABSCHLUSS_NR Integer);
ALTER TABLE KASSE_BONPOS ADD PRIMARY KEY (BON_ID,POS_ZEILE);

And the other table:

CREATE TABLE KASSE_BONPOS_UST (
BON_ID KASSE_Integer NOT NULL,
POS_ZEILE Integer NOT NULL,
Z_KASSE_ID Varchar(30),
KASSENABSCHLUSS_NR Integer,
POS_BRUTTO Numeric(15,2));

Answer 1

Here's a working solution:

select pu.KASSENABSCHLUSS_NR,
sum(case
    when p.GV_TYP = 14 then pu.POS_BRUTTO 
    else Null 
  end
 ) as Summe
from KASSE_BONPOS_UST pu
  left join KASSE_BONPOS p
  on (pu.Z_KASSE_ID = 'MeineKasse') and
     (pu.KASSENABSCHLUSS_NR = p.KASSENABSCHLUSS_NR) and
     (pu.BON_ID = p.BON_ID)
  group by pu.KASSENABSCHLUSS_NR
  order by pu.KASSENABSCHLUSS_NR