SQL Sum Data from Multiple Tables

SQL: How to to SUM two values from different tables

select region,sum(number) total
from
(
    select region,number
    from cash_table
    union all
    select region,number
    from cheque_table
) t
group by region

SQL How to use SUM and Group By with multiple tables?

Here is a solution based on your data. Issue with your query is that you were joining tables on a non-unique column resulting in Cartesian product.

Data

DROP TABLE IF EXISTS A;

CREATE TABLE A
(num int,
 id int,
 col1 int);

 INSERT INTO A VALUES (1,     100,     0);
 INSERT INTO A VALUES (2,     100,     1);
 INSERT INTO A VALUES (3,     100,     0);
 INSERT INTO A VALUES (1,     101,     1);
 INSERT INTO A VALUES (2,     101,     1);
 INSERT INTO A VALUES (3 ,    101,     0);

DROP TABLE IF EXISTS B;

 CREATE TABLE B
(idx int,
 id int,
 col2 int);

 INSERT INTO B VALUES (1,     100,     20);
 INSERT INTO B VALUES (2,     100,     20);
 INSERT INTO B VALUES (3,     100,     20);
 INSERT INTO B VALUES (4,     101,     100);
 INSERT INTO B VALUES (5,     101,     100);

DROP TABLE IF EXISTS C;

CREATE TABLE C
(idx int,
 id int,
 col3 int);

 INSERT INTO C VALUES (1,     100,     1);
 INSERT INTO C VALUES (2,     100,     1);
 INSERT INTO C VALUES (3,     100,     1);
 INSERT INTO C VALUES (4,     101,     10);
 INSERT INTO C VALUES (5,     101,     1);

Solution

SELECT a_sum.id, col1_sum, col2_sum, col3_sum
 FROM (SELECT id, SUM(col1) AS col1_sum
         FROM a
        GROUP BY id ) a_sum
      JOIN 
      (SELECT id, SUM(col2) AS col2_sum
         FROM b
        GROUP BY id ) b_sum 
      ON (a_sum.id = b_sum.id)
      JOIN 
      (SELECT id, SUM(col3) AS col3_sum
         FROM c
        GROUP BY id ) c_sum 
      ON (a_sum.id = c_sum.id);

Result is as expected

Note: Do outer joins if an id doesnt have to be present in all three tables.

SQL Sum and Count JOIN Multiple tables

Seems the only thing i need is to subquery it

select a.*, d.image, b.totalRating, c.totalUser from places a, ( select place_id, sum(rating) AS totalRating from ratings group by place_id ) b, ( select place_id, count(id) AS totalUser from ratings group by place_id ) c, places_images d where c.place_id = a.id and b.place_id = a.id and d.place_id = a.id GROUP BY a.id

Result

Thank you very much, GBU

sql - getting sum of same column from multiple tables

You can achieve it using Coalesce as follows

SELECT 
    (SELECT coalesce(SUM(value),0) FROM table1) + 
    (SELECT coalesce(SUM(value),0) FROM table2) + 
    (SELECT coalesce(SUM(value),0) FROM table3) as total_sum

Another approach is to use union all to merge all values into single table

select distinct coalesce(sum(a.value), 0) as total_sum from
    (select value from table1
      union all
    select value from table 2
      union all
    select value from table 3) a;

SQL aggregate data from multiple tables and sum a specified column

As you noted, union will remove duplicates, so you should use union all. Once you do that, you can wrap that query with an aggregate query to get the sum of the counts:

SELECT   user_id, user_label, code, SUM(cnt) AS total_count
FROM     (SELECT user_id, user_label, code1 as code, count1
          FROM   table1
          UNION ALL
          SELECT user_id, user_label, code2, count2
          FROM   table2
          UNION ALL
          SELECT user_id, user_label, code3, count3
          FROM   table3) t
GROUP BY user_id, user_label, code

MS SQL SUM() using multiple tables and adding only last recorded value

That's great work, Carlos.

I was able to add to your code to get the results I need

There is input of which machine is displaying. :machine is the input.

SELECT ISNULL((
SELECT sum(Hours)
FROM (SELECT 
        (SELECT TOP 1 change.hours 
            FROM change WHERE change.id = part.id
            ORDER BY change.timeStamp DESC) as 'Hours'
    FROM change 
        INNER JOIN part ON (part.id = change.id)
        INNER JOIN  completed ON (part.id = completed.id) 
    
    WHERE part.id NOT IN (SELECT completed.id FROM completed WHERE completed.completed = 1) 
    and (SELECT TOP 1 change.machine FROM change WHERE part.id = change.id ORDER BY change.timeStamp DESC) = :machine
    
    GROUP BY change.id, part.id) as a), 0)

Sum columns, count rows, with multiple tables to display data altogether

Although I'm not really sure about your table structure for Billing and Forwards, the easiest way to accomplish this would be to use CTEs to first sum all the values in those 2 tables, then just use those values in your final query like so:

WITH TotalBilling
AS
(
    SELECT
         OrderId
        ,SUM(Amount) Amount
    FROM
        Billing
    WHERE
        PaidFull = 'FALSE'
    GROUP BY
        OrderId
),

TotalForwards
AS
(
    SELECT
         OrderId
        ,SUM(Amount) Amount
    FROM
        Forwards
    WHERE
        PaidFull = 'FALSE'
    GROUP BY
        OrderId
)

SELECT
     SUM(B.Amount)                           AS Billing
    ,COUNT(B.OrderId)                        AS Qty
    ,SUM(F.Amount)                           AS Forwards
    ,COUNT(F.OrderId)                        AS Qty
    ,COALESCE(Cl.ClientName, Co.CompanyName) AS "Client / Company Name"
FROM
    Orders O
    LEFT JOIN Clients Cl
    ON O.ClientId = Cl.ClientId
    LEFT JOIN Companies Co
    ON O.CompanyId = Co.CompanyId
    LEFT JOIN TotalBilling B
    ON B.OrderId = O.OrderId
    LEFT JOIN TotalForwards F
    ON F.OrderId = O.OrderId
GROUP BY
    COALESCE(Cl.ClientName, Co.CompanyName)
ORDER BY
     COALESCE(Cl.ClientName, Co.CompanyName)

Here is a SqlFiddle showing how it would look.

SUM from Multiple tables?

Try below with sum aggregation

SELECT T0.[CardName] AS "Customer Name"
, T1.[ItemCode] AS "Item Code"
, sum(T1.[Quantity]) AS "Quantity" 
FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
WHERE T2.[QryGroup7] = 'Y' AND (T0.DocDate>='[%0]' and T0.DocDate<='[%1]')
group by T0.[CardName],T1.[ItemCode]
ORDER BY T0.[CardName], T1.[ItemCode]

---

Related Topics