Summing and counting across multiple tables in SQL

Question

I have 3 tables from which I need to select and summarize data.

Table: IDEA  
REFERENCE SL   
128       SL1  
200       SL1  
201       SL2  
205       SL3 



Table: ACCT1  
IDEA_REF  ACCTS  
128       5  
128       2  
200       3  
205       4  

Table: ACCT2  
IDEA_REF  ACCTS  
201       3  
205       4 
205       3

What I need to do is pull a summary sorted by SL with the totals from the ACCTS field of both tables.

Here is the SQL I am using so far:

SELECT I.SL AS SL, COUNT(DISTINCT I.REFERENCE) AS NO,  
    SUM(CASE WHEN A1.IDEA_REF=I.REFERENCE THEN A1.ACCTS ELSE 0 END) AS ACCT1,  
    SUM(CASE WHEN A2.IDEA_REF=I.REFERENCE THEN A2.ACCTS ELSE 0 END) AS ACCT2  
    FROM IDEA I  
    LEFT JOIN ACCT1 A1 ON A1.IDEA_REF=I.REFERENCE  
    LEFT JOIN ACCT2 A2 ON A2.IDEA_REF=I.REFERENCE  
    WHERE A2.IDEA_REF IN I.REFERENCE OR A1.IDEA_REF IN I.REFERENCE  
    GROUP BY I.SL  

The problem I am finding is when there are multiple values in the ACCT1 and ACCT2 tables with reference to the IDEA table. Here are the results from this query:

SL  NO  ACCT1  ACCT2  
SL1 2   10      0  
SL2 1    0      3  
SL3 1    8      7  

The SL3 line adds the ACCT1 and ACCT2 values two times. I can't seem to find the right way to add them the appropriate number of times.

The desired output is:

SL   NO   ACCT1  ACCT2
SL1  2    10     0  
SL2  1     0     3  
SL3  1     4     7

Any help would be much appreciated.

Answer 1

You hare asking for three separate aggregates, but you're trying to compute them all in a single query.

To get your NO (count of distinct items) you can do

SELECT SL,
       COUNT(*) AS NO
  FROM IDEA
 GROUP BY SL

To get your ACCT1 item you can do:

SELECT SL,
       SUM(ACCTS) AS ACCT1
  FROM IDEA
  JOIN ACCT1 ON IDEA.REFERENCE = ACCT1.IDEA_REF
GROUP BY SL

In like manner you can get ACCT2

SELECT SL,
       SUM(ACCTS) AS ACCT2
  FROM IDEA
  JOIN ACCT2 ON IDEA.REFERENCE = ACCT2.IDEA_REF
GROUP BY SL

Then, you need to join all these aggregate queries together on SL to get your result set. Because you have missing entries in some of the aggregates, you need the LEFT in LEFT JOIN and the COALESCE() items.

SQL Fiddle

Here's the over all query

SELECT Q.SL, NO, 
       COALESCE(ACCT1,0) AS ACCT1, 
       COALESCE(ACCT2,0) AS ACCT2
  FROM (
        SELECT SL,
               COUNT(*) AS NO
          FROM IDEA
          GROUP BY SL
       ) Q
   LEFT JOIN (
          SELECT SL,
                 SUM(ACCTS) AS ACCT1
            FROM IDEA
            JOIN ACCT1 ON IDEA.REFERENCE = ACCT1.IDEA_REF
           GROUP BY SL
        ) R ON Q.SL = R.SL
   LEFT JOIN (
            SELECT SL,
                   SUM(ACCTS) AS ACCT2
              FROM IDEA
              JOIN ACCT2 ON IDEA.REFERENCE = ACCT2.IDEA_REF
            GROUP BY SL
         ) S ON Q.SL = S.SL

The result is what you are looking for:

|  SL | NO | ACCT1 | ACCT2 |
|-----|----|-------|-------|
| SL1 |  2 |    10 |     0 |
| SL2 |  1 |     0 |     3 |
| SL3 |  1 |     4 |     7 |

See how this works? You have to do each aggregate separately.

If you're using a DBMS that doesn't know the JOIN ... USING() syntax, put in ON Q.SL = R.SL or the appropriate ON clause instead. See the edit, and see this fiddle: http://sqlfiddle.com/#!2/63aa1/3/0