SQL Problem

[manfred_h]

Hallo zusammen

mit folgendem SQL-Code hole ich mir Daten aus der DB:

SQL-Code:

SELECT 
    C_NO,
    (CASE
      WHEN TODO_TYPE = 'Member_Dinner'
       THEN '1' ELSE 0 END ) MD,
    (CASE
      WHEN TODO_TYPE = 'Member_Fee'
       THEN '1' ELSE 0 END ) MF,
    (CASE
      WHEN TODO_TYPE = 'Target_Set'
       THEN '1' ELSE 0 END ) TS,
    (CASE
      WHEN TODO_TYPE = 'Annual_Report'
       THEN '1' ELSE 0 END ) AR,
    (CASE
      WHEN TODO_TYPE = 'Elections_to_HQ'
       THEN '1' ELSE 0 END ) E_HQ,
    (CASE
      WHEN TODO_TYPE = 'N_M_P'
       THEN '1' ELSE 0 END ) N_M_P,
    (CASE
      WHEN TODO_TYPE = 'Pastors_Banquets'
       THEN '1' ELSE 0 END ) PB,
    (CASE
      WHEN EXTRACT (MONTH FROM TODO_DATE ) BETWEEN 1 AND 5
       THEN EXTRACT (YEAR FROM TODO_DATE) 
      ELSE
       EXTRACT (YEAR FROM TODO_DATE) + 1 END ) B_YEAR
FROM 
    CAMPS_D_TODO

Das Ergebnis sieht dann So aus:

Delphi-Quellcode:

C_NO   MD   MF   TS   AR   E_HQ   N_M_P   PB   B_YEAR
I47705   0   1   0   0   0   0   0   2009
I47705   1   0   0   0   0   0   0   2009
I47705   0   0   0   0   1   0   0   2009
I47705   0   0   0   0   0   1   0   2009
I47705   0   0   1   0   0   0   0   2009
I47705   0   0   0   0   0   0   1   2009
I47705   0   0   0   1   0   0   0   2010

Nun möchte ich das die Spalten MD / MF / TS / AR / E_HQ / N_M_P und PB jeweils summiert werden.
Das Ergebnis sollte dann so aussehen:

Delphi-Quellcode:

C_NO   MD   MF   TS   AR   E_HQ   N_M_P   PB   B_YEAR
I47705   1   1   1   1   1   1   1   2009
I47705   0   0   0   1   0   0   0   2010

Hat mir da jemand einen Tipp.
Mit GROUP BY und SUM habe ich schon rumgetestet aber nicht das Ergebnis erhalten...

Danke für Eure Tipps.
Manfred

[IIIMADDINIII]

kann man da nicht einfach eine schleife schreiben die dann alle zeilen durchgeht

[mkinzler]

SQL-Code:

SELECT
    C_NO,
    sum(iif ( TODO_TYPE = 'Member_Dinner', 1, 0)) as MD,
    ...
from 
    CAMPS_D_TODO
group by
   C_NO, B_YEAR;

[manfred_h]

Wow, das ging schnellll super

das wars:

SQL-Code:

SELECT 
    C_NO,
    SUM(IIF ( TODO_TYPE = 'MEMBER_FEE', 1, 0)) AS MF,
    SUM(IIF ( TODO_TYPE = 'TARGET_SET', 1, 0)) AS TS,
    SUM(IIF ( TODO_TYPE = 'ANNUAL_REPORT', 1, 0)) AS AR,
    SUM(IIF ( TODO_TYPE = 'ELECTIONS_TO_HQ', 1, 0)) AS E_HQ,
    SUM(IIF ( TODO_TYPE = 'N_M_P', 1, 0)) AS N_M_P,
    SUM(IIF ( TODO_TYPE = 'PASTORS_BANQUETS', 1, 0)) AS PB,
    SUM(IIF ( TODO_TYPE = 'MEMBER_DINNER', 1, 0)) AS MD, 
    (CASE
      WHEN EXTRACT (MONTH FROM TODO_DATE ) BETWEEN 1 AND 5
       THEN EXTRACT (YEAR FROM TODO_DATE) 
      ELSE
       EXTRACT (YEAR FROM TODO_DATE) + 1 END ) B_YEAR
FROM 
    CAMPS_D_TODO
GROUP BY
   C_NO, B_YEAR;

Besten Dank Euch beiden!
Shalom
Manfred