CODEWITHSUNDEEP
sqljoin
DanielST
DanielST

Reputation: 14133

SQL multiple joins on the same table

So i have a table dpgift. I need to generate 24 different "stats" (counts and sums with filters) based on the table.

The below should generate the first three columns.

select a.lc as listCode, a.c, NVL(b.c,0), NVL(c.c,0)
    FROM (select list_code as lc, count(donor) as c
              FROM dpgift 
              group by list_code) a
        LEFT JOIN (select list_code as lc, count(donor) as c
                       FROM dpgift
                       WHERE dpgift.rectype="P" 
                       group by list_code) b
        LEFT JOIN (select list_code as lc, count(donor) as c
                       FROM dpgift
                       WHERE dpgift.rectype="G" 
                       group by list_code) c
            ON b.lc = c.lc 
            ON a.lc = b.lc

This almost works but something is wrong with my joins. The third column (c.c), only has values where b.c is not null.

Can someone tell me what's wrong?

btw, i'm working in VFP but sql seems to work pretty standard.

edit: ok here's the output i'm getting

listcode  c    exp_3 Exp_4
482       14   0     0
483       2    0     0
499       852  1     857
....

It should be:

listcode  c    exp_3 Exp_4
482       14   0     14
483       2    0     2
499       852  1     857
....

Upvotes: 0

Views: 362

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

This version should produce what you're looking for.

SELECT list_code,
       COUNT(donor),
       SUM(CASE WHEN rectype = 'P' THEN 1 ELSE 0 END) AS Pcount,
       SUM(CASE WHEN rectype = 'G' THEN 1 ELSE 0 END) AS Gcount
    FROM dpgift
    GROUP BY list_code

Upvotes: 4

Related Questions