CODEWITHSUNDEEP
sqlsql-serverperformance
mntyguy
mntyguy

Reputation: 187

SQL SUM of Totals from different tables in single query

Not sure if this is the best way to do this, but I'll try to give an example to explain what I am trying to accomplish. I have about 4 or 5 different tables that each contain a TOTAL field. One table contains a CUSTOMER_ID (each of the 4 or 5 other tables contain a foreign key that links their records to the parent CUSTOMER table).

I want to group by CUSTOMER_ID in one column in my query while each of the other columns contains the overall total for the respective table.

Does this make sense? I'm looking for the most efficient and properly designed query. It sounds like I would need sub-query rather than a bunch of left outer joins?

Upvotes: 0

Views: 2151

Answers (3)

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

Here's another possibility. It doesn't require deriving any tables then joining on them. This should also be efficient enough, assuming that the FK on table1-table5 is the CUSTOMER_ID...Not 100% sure of that though. Also in my experience when left joining, it's typically a good idea to use ISNULL so you always get some numeric value (although in this case it is another assumption on my part). Hope this helps!

SELECT  
C.CUSTOMER_ID,
    ISNULL(SUM(T1.TOTAL), 0) AS TOTAL_T1,
    ISNULL(SUM(T2.TOTAL), 0) AS TOTAL_T2,
    ISNULL(SUM(T3.TOTAL), 0) AS TOTAL_T3,
    ISNULL(SUM(T4.TOTAL), 0) AS TOTAL_T4,
    ISNULL(SUM(T5.TOTAL), 0) AS TOTAL_T5
FROM CUSTOMER C
    LEFT JOIN TABLE1 T1 ON C.CUSTOMER_ID = T1.CUSTOMER_ID
    LEFT JOIN TABLE2 T2 ON C.CUSTOMER_ID = T2.CUSTOMER_ID
    LEFT JOIN TABLE3 T3 ON C.CUSTOMER_ID = T3.CUSTOMER_ID
    LEFT JOIN TABLE4 T4 ON C.CUSTOMER_ID = T4.CUSTOMER_ID
    LEFT JOIN TABLE5 T5 ON C.CUSTOMER_ID = T5.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID

Upvotes: 0

neiha
neiha

Reputation: 171

not sure if I get what correctly what you are asking but I think you can accomplish this with simple joins, for two tables

select table1.customerId, sum(table1.total) as total1, sum(table2.total) as total2
FROM table1, table2
where table1.customerId=table2.customerId
group by table1.customerId;

and you can make it with as many tables as you want

Upvotes: 0

Lamak
Lamak

Reputation: 70638

SELECT  C.CUSTOMER_ID,
        T1.TOTAL TOTAL_T1,
        T2.TOTAL TOTAL_T2,
        T3.TOTAL TOTAL_T3,
        T4.TOTAL TOTAL_T4,
        T5.TOTAL TOTAL_T5
FROM CUSTOMER C
LEFT JOIN ( SELECT CUSTOMER_ID, SUM(TOTAL) TOTAL)
            FROM TABLE1
            GROUP BY CUSTOMER_ID) T1
    ON C.CUSTOMER_ID = T1.CUSTOMER_ID
LEFT JOIN ( SELECT CUSTOMER_ID, SUM(TOTAL) TOTAL)
            FROM TABLE2
            GROUP BY CUSTOMER_ID) T2
    ON C.CUSTOMER_ID = T2.CUSTOMER_ID
LEFT JOIN ( SELECT CUSTOMER_ID, SUM(TOTAL) TOTAL)
            FROM TABLE3
            GROUP BY CUSTOMER_ID) T3
    ON C.CUSTOMER_ID = T3.CUSTOMER_ID
LEFT JOIN ( SELECT CUSTOMER_ID, SUM(TOTAL) TOTAL)
            FROM TABLE4
            GROUP BY CUSTOMER_ID) T4
    ON C.CUSTOMER_ID = T4.CUSTOMER_ID
LEFT JOIN ( SELECT CUSTOMER_ID, SUM(TOTAL) TOTAL)
            FROM TABLE5
            GROUP BY CUSTOMER_ID) T5
    ON C.CUSTOMER_ID = T5.CUSTOMER_ID

Upvotes: 3

Related Questions