SQL Join, Group by on Three Tables to Get Totals

Inner Join for 3 tables with SUM of two columns in SQL Query?

PROBLEM

The problem here is that there are consecutive inner joins and the number of rows getting fetched in the second inner join is not restricted. So, as we have not added a condition on sales_payment_id in the join between the sales and sales_payment tables, one row in sales table(for customer_id 2, in this case) would be mapped to 2 rows in the payment table. This causes the same values to be reconsidered.
In other words, the mapping for customer_id 2 between the 3 tables is 1:1:2 rather than 1:1:1.

SOLUTION

Solution 1 : As mentioned by Gordon, you could first aggregate the amount values of the sales_payments table and then aggregate the values in sales table.

Solution 2 : Alternatively (IMHO a better approach), you could add a foreign key between sales and sales_payment tables. For example, the sales_payment_id column of sales_payment table can be introduced in the sales table as well. This would facilitate the join between these tables and reduce additional overheads while querying data.

The query would then look like:

`SELECT c.customer_id,
  c.name,
  SUM(s.total),
  s.created_at,
  SUM(sp.amount)
FROM customer c
INNER JOIN sales s
ON c.customer_id = s.customer_id
INNER JOIN sales_payments sp
ON c.customer_id        = sp.customer_id
AND s.sales_payments_id = sp.sales_payments_id
WHERE s.created_at      ='2020-04-03'
GROUP BY c.customer_id,
c.name,
s.created_at ;`

Hope that helps!

LEFT JOIN 3 tables with GROUP BY and SUM

I think you can try this -

select tu.userid,
       tu.UserName,
       tdl.ProductKey,
       tp.ProductName,
       sum(tdl.Counter) as Total
  from tblusers tu
  left join tbldailylog tdl
    on tu.userid = tdl.userid
  left join tblproducts tp
    on tu.userid = tp.userid
 group by tu.userid, tu.UserName, tdl.ProductKey, tp.ProductName
 order by sum(tdl.counter) desc

How to join three tables to get Sum

I don't think your sample data is really what you have based on the information provided. My best guess here is that your query is doing a fan-out on either or both of those joins which is messing up your sums. You need to sum them separately, else additional rows in either on of those joins will fan out the other join, duplicating your results in the sum. This is evident in your result since 001 looks to be double (even though your sample data doesn't show it).

Something like this would ensure sums independent of each other:

SELECT PR.product_no, 
    ( SELECT sum(I.qty)
      FROM invoice I 
      WHERE I.product_no=PR.product_no ) invoice_qty,
    ( SELECT sum(P.qty)
      FROM purchase P 
      WHERE P.product_no=PR.product_no ) purchase_qty
FROM products PR 

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

Joining 3 tables to get total target and total sales

Try This-

SELECT A.smanTeam,
A.TotalTarget,
B.TotalSales,
B.TotalSales*100/A.TotalTarget TotalPercentage
FROM
(
    SELECT smanTeam,SUM(Target) TotalTarget
    FROM Sman S
    INNER JOIN SalesTarget ST ON S.smanID  = ST.smanID
    GROUP BY smanTeam
)A
LEFT JOIN
(
    SELECT smanTeam, SUM(Amount) TotalSales
    FROM Sman S
    INNER JOIN Sales SA ON S.smanID  = SA.smanID
    GROUP BY smanTeam
)B ON A.smanTeam = B.smanTeam

LEFT JOIN 3 tables with group by and get sum

The issue with your query is that when you combine t_customer with both t_order and t_payment, you get double combinations. Take a look at the output of this query to understand what I mean:

SELECT c.customer_id,
       c.customer_name,
       o.order_amount,
       p.pay_amount
FROM      t_customer c
INNER JOIN t_order o 
        ON c.customer_id = o.customer_id
INNER JOIN t_payment p 
        ON c.customer_id = p.customer_id

In order to avoid this issue, you can move the aggregation operations before the join operations:

SELECT c.customer_id,
       c.customer_name,
       COALESCE(o.total_order_amount, 0)   AS order_amount,
       COALESCE(p.total_pay_amount,   0)   AS pay_amount
FROM      t_customer c
LEFT JOIN (SELECT customer_id,
                  SUM(order_amount)  AS total_order_amount
           FROM t_order
           GROUP BY customer_id) o 
       ON c.customer_id = o.customer_id
LEFT JOIN (SELECT customer_id,
                  SUM(pay_amount)    AS total_pay_amount
           FROM t_payment
           GROUP BY customer_id) p 
       ON c.customer_id = p.customer_id

Check the demo here.

JOIN 3 tables SUM group by month

Calculate sum for invoice and purchase in different sub clause and then do a left join with your month table

SELECT m.month_name as month, 
   i.total_inv, 
   p.total_pur
FROM month m
LEFT JOIN (SELECT MONTH(date_inv) as month, 
            SUM(subtotal) as total_inv  
            FROM table_invoice
            WHERE YEAR(date_inv)= '2018'
            GROUP BY month) i  ON (m.month_num = i.month)    
LEFT JOIN (SELECT MONTH(date_pur) as month, 
            SUM(subtotal) as total_pur
            FROM  table_purchase 
            WHERE YEAR(date_pur)= '2018'
            GROUP BY month) p ON (m.month_num = p.month )
ORDER BY m.month_num

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.

MySQL query - joining 3 tables count and group by one column

Your subquery makes no sense to me. The entire query is as simple as:

SELECT events.id, order_items.category, SUM(order_items.quantity) AS count FROM events left join orders on events.id=orders.event_id LEFT JOIN order_items ON order_items.order_id = orders.id GROUP BY events.id, order_items.category

I think the key here is that you want to group by two columns. Grouping by event.id should be the same as event.title as long as title is unique. But you also need to group by category to get the proper total. And since you want the count, you actually need to include that in the columns you select.

But, order.id is not the same as event.id, event.title is not the same as order_items.category, and a.title is not the count.

---

Related Topics