Sql Sum Group by Two Tables

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 GROUP BY two tables

For your query, to get the two rows grouped on one row, you can try grouping on the account number AND the balance:

SELECT  T.account_no
        ,A.balance
        ,SUM(T.amount) AS TotalAmount
        ,(A.balance + SUM(T.amount)) AS "CORRECT BALANCE"
FROM    transactions AS T
INNER JOIN account AS A ON T.account_no = A.account_no
GROUP BY T.account_no, A.balance;

(By the way, I've used the ANSI join instead of the 'old' way of joining tables, because it's much more clear what you're doing that way.)

EDIT

To make things a bit more clear, I've made a SQL Fiddle. Does this represent your situation more or less correctly?

EDIT2

The above query would not show any accounts without transactions, as Kaf commented. That might be what you want, but in case it's not you can switch the join tables like this:

SELECT  A.account_no
        ,A.balance
        ,SUM(T.amount) AS TotalAmount
        ,(A.balance + SUM(T.amount)) AS "CORRECT BALANCE"
FROM    account AS A
LEFT OUTER JOIN transactions AS T ON T.account_no = A.account_no
GROUP BY A.account_no, A.balance;

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

SUM multiple tables GROUP BY column

I used a common table expression cte_car to make a first grouping and get all the details of a single car. Then a final grouping is done to get the totals accross the car types.

Sample data

I left out the Maintenances table because it is not needed for the consumptions.

create table Cars
(
  CarID int,
  CarType varchar(50),
  PlateNo varchar(20)
);

insert into Cars (CarID, CarType, PlateNo) values 
(1,'Coupe','BC18341'),
(2,'Hatchback','AU14974'),
(3,'Hatchback','BC49207'),
(4,'SUV','AU10299'),
(5,'Coupe','AU32703'),
(6,'Coupe','BC51719'),
(7,'Hatchback','AU30325'),
(8,'SUV','BC52018');

create table Fuelings
(
  CarID int,
  FuelingDate date,
  Odometer int,
  Quantity decimal,
  Cost money
);

insert into Fuelings (CarID, FuelingDate, Odometer, Quantity, Cost) values 
(1,'2021-01-02', 124520, 53.28, 78.32),
(1,'2021-01-15', 124810, 49.17, 68.34),
(1,'2021-01-28', 125130, 51.74, 69.13),
(2,'2021-01-05', 344380, 49.10, 72.81),
(2,'2021-01-18', 344540, 54.98, 69.37),
(2,'2021-01-29', 344990, 52.76, 66.83),
(3,'2021-01-01', 874200, 45.27, 73.48),
(3,'2021-01-19', 874770, 46.75, 67.91),
(3,'2021-01-26', 874930, 52.15, 75.50),
(4,'2021-01-03', 414190, 50.88, 71.72),
(4,'2021-01-14', 414400, 51.94, 68.15),
(4,'2021-01-29', 415140, 48.30, 77.82),
(5,'2021-01-06', 294240, 48.15, 71.48),
(5,'2021-01-19', 294680, 53.86, 66.80),
(5,'2021-01-30', 294890, 51.54, 74.31),
(6,'2021-01-01', 934220, 49.26, 69.98),
(6,'2021-01-18', 934520, 51.35, 71.50),
(6,'2021-01-25', 934970, 54.63, 65.72),
(7,'2021-01-05', 584110, 51.42, 74.29),
(7,'2021-01-22', 584430, 49.36, 69.95),
(7,'2021-01-31', 584750, 49.84, 73.18),
(8,'2021-01-02', 654280, 53.87, 77.75),
(8,'2021-01-17', 654730, 45.32, 67.48),
(8,'2021-01-29', 654930, 50.75, 69.80);

Solution

with cte_car as
(
  select c.CarId,
         c.CarType,
         max(f.Odometer) - min(f.Odometer) as CarDistance,
         sum(f.Quantity) as CarQuantity
  from Cars c
  join Fuelings f
    on f.CarId = c.CarId
  group by c.CarId,
           c.CarType
)
select cc.CarType,
       sum(cc.CarDistance) as TotalDistance,
       sum(cc.CarQuantity) as TotalQuantity,
       sum(cc.CarQuantity) * 100.0 / sum(cc.CarDistance) as TotalConsumption
from cte_car cc
group by cc.CarType;

Result

CarType TotalDistance TotalQuantity TotalConsumption
------- ------------- ------------- ----------------
Coupe            2010           463        23.034825
Hatchback        1980           451        22.777777
SUV              1600           301        18.8125

Fiddle to see things in action.

SQL: How to group by with two tables?

You should only be grouping by the attributes you need to be aggregated. In this case, you need only products.name.

SELECT
      products.name,
      sum(history.amount) AS [Amount]
FROM history
INNER JOIN products ON history.id_product = products.id_product
GROUP BY
      products.name;

If you need to include products without history (assuming sum should be 0 instead of null in this case), then you can use an OUTER JOIN instead of INNER JOIN to include all products:

SELECT
      products.name,
      COALESCE(sum(history.amount), 0) AS [Amount]
FROM history
    RIGHT OUTER JOIN products ON history.id_product = products.id_product
GROUP BY
      products.name;

Select the sum of values from 2 tables and group by common foreign key

Is this what you want?

select u.*,
       (select sum(a.score) from a where a.id_user = u.id_user) as a_sum,
       (select sum(b.score) from b where b.id_user = u.id_user) as b_sum
from user u;

If you want the total score, just add them together:

select u.*,
       ( (select COUNT(1)  from a where a.id_user = u.id_user) +
         (select coalesce(sum(b.score), 0) from b where b.id_user = u.id_user)
       ) as ab_sum
from user u;

Select SUM, Join two tables, group sum by OrderID in SQL Query

Using the example data you provided through w3c:

SELECT o.OrderID, DATEPART(YEAR,o.OrderDate) AS OrderYear, DATEPART(QUARTER,o.OrderDate) AS OrderQuarter,
       E.FirstName + ' ' + E.LastName AS EmployeeName, C.CustomerName, S.ShipperName, SUM(d.Quantity) AS Quantity--, SUM(d.Quantity*d.Price) AS OrderRevenue
  FROM Orders o
    INNER JOIN Employees e
      ON o.EmployeeID = e.EmployeeID
    INNER JOIN Customers c
      ON o.CustomerID = c.CustomerID
    INNER JOIN Shippers s
      ON o.ShipperID = s.ShipperID
    INNER JOIN OrderDetails d
      ON o.OrderID = d.OrderID
 GROUP BY o.OrderID, DATEPART(YEAR,o.OrderDate), DATEPART(QUARTER,o.OrderDate),
       E.FirstName + ' ' + E.LastName, C.CustomerName, S.ShipperName

OrderID OrderYear   OrderQuarter    EmployeeName        CustomerName            ShipperName     Quantity
--------------------------------------------------------------------------------------------------------
10249   1996        3               Michael Suyama      Tradicao Hipermercados  Speedy Express  49
10250   1996        3               Margaret Peacocl    Hanari Carnes           United Package  60
10368   1996        4               Andrew Fuller       Ernst Handel            United Package  78
10389   1996        4               Margaret Peacock    Bottom-Dollar Marketse  United Package  81
10418   1996        1               Margaret Peacock    QUICK-Stop              Speedy Express  146
10442   1997        1               Janet Leaverling    Ernst Handel            United Package  170

Your syntax error was because you did not define a GROUP BY and reference the columns which you wanted to aggregate to. When you use an aggregate function (like SUM or COUNT) you must also tell the engine which columns you will be grouping by.

SUM SQL multiple tables GROUP BY

If you ignore PLACE in your joins then you will get the wrong answer. Include PLACE in the join logic!

SELECT horse_name, horse.horse_id, SUM(prizemoney) 
FROM horse 
JOIN entry ON entry.horse_id=horse.horse_id 
JOIN prize ON entry.event_id=prize.event_code 
          AND entry.place = prize.place
GROUP BY horse_name, horse.horse_id

also: include ALL non-aggregating columns in the grouop by clause

SQL Joint and Group two tables with SUM function

Well, there are two ways to write this depending on what you want

Adding the Quantity to the aggregate...

SELECT 
   P.ProductID, 
   SUM(P.Quantity), 
   SUM(OL.Amount)
FROM atbl_Sales_Products AS P
LEFT JOIN atbl_Sales_OrdersLines AS OL ON OL.ProductID = P.ProductID 
GROUP BY P.ProductID

Adding the quantity to the grouping

SELECT 
   P.ProductID, 
   P.Quantity, 
   SUM(OL.Amount)
FROM atbl_Sales_Products AS P
LEFT JOIN atbl_Sales_OrdersLines AS OL ON OL.ProductID = P.ProductID 
GROUP BY P.ProductID, P.Quantity
--based on comment
HAVING SUM(OL.Amount) > P.Quantity

---

Related Topics