Join Tables With Sum Issue in MySQL

MYSQL - Using SUM with JOIN

try this

   select groups.id,groups.name,users.name,sum(content.duration) as duration from groups
   join users on groups.owner=users.id
   join items on items.group=groups.id
   join content on content.id=items.content
   group by groups.name

MySQL merge two tables and get sum

So, instead of JOIN what you need is UNION. You can use "UNION ALL" or "UNION", it depends if you want the duplicated rows or not.

In any case, after the UNION, group that result into a subquery to get the SUM()

SELECT
u.name,
u.code,
SUM(u.num),
FROM
(
SELECT name, code, num FROM tableA
UNION ALL
SELECT name, code, num FROM tableB
) u
GROUP BY u.name, u.code

MySql Join Tables With Sum Of A Column

If I'm understanding correctly, I believe the following should work.

The key is to issue the LEFT JOIN so that even a server with no matching record in the server_hit table will still show in the final output, but with a 0 sum.

SELECT s.server_id, s.server_name, s.server_url, s.category_id, c.category_name, IFNULL(SUM(sh.hit_count), 0)
FROM server s
INNER JOIN category c ON s.category_id = c.category_id
LEFT JOIN server_hit sh ON s.server_id = sh.server_id
GROUP BY s.server_id, s.server_name, s.server_url, s.category_id, c.category_name

Add IF EXISTS to handle NULL issue

SELECT DISTINCT s.server_id, s.server_name, s.server_url, s.category_id, c.category_name, IF(EXISTS(SELECT id FROM server_hit WHERE sh.server_id = s.server_id), SUM(sh.hit_count), 0) as 'total_hit_count' 
FROM server s 
INNER JOIN category c ON s.category_id = c.category_id 
LEFT JOIN server_hit sh ON s.server_id = sh.server_id GROUP BY s.server_id, s.server_name, s.server_url, s.category_id, c.category_name

Sum of column is not working in join table PHP

Edit to select data when table1 and table2 are empty

SELECT table1.id, table3.sku_code as sku, table3.sku_name, 
       sum(table1.req_qty) as reqQty, sum(table2.in_qty) as inQty
  FROM table3
  LEFT JOIN table1 on table3.sku_code = table1.sku_1
  LEFT JOIN table2 on table3.sku_code = table2.sku_2
  GROUP BY table1.id, table3.sku_code, table3.sku_name

Explanation

You can see an explanation on how left join works here https://www.w3schools.com/sql/sql_join_left.asp#:~:text=The%20LEFT%20JOIN%20keyword%20returns,if%20there%20is%20no%20match.

But to explain this query quickly, we will select all data from table3, left join will find all records from left table (here table3) and mathcing records from right tables (here table2 and table 1).

MySQL SUM function in multiple joins

You want to know if you can do this without subqueries. No, you can't.

If a row in Charges has more than one corresponding row in Taxes, you can't simply join the tables without duplicating Charges rows. Then, as you have discovered, when you sum them up, you'll get multiple copies.

You need a way to get a virtual table (a subquery) with one row for each Charge.

                    SELECT ch.customer_id,
                           ch.amount amount,
                           tx.tax tax
                      FROM Charges
                      LEFT JOIN (  
                                  SELECT SUM(amount) tax,
                                         charge_id 
                                    FROM Taxes
                                   GROUP BY charge_id
                          ) tx ON ch.id = tx.charge_id

You can then join that subquery to your Customer table to summarize sales by customer.

JOIN, GROUP BY, SUM Issue Mysql

The inner subquery groups them normally and then the main query is what deals with limiting the results.

SELECT * FROM 
     (select 
      b.id,
      b.name as name, 
      SUM(a.value) as the_sum
      from tableA a inner join tableB b 
      on a.Id = b.id 
      group by b.name, b.id
     ) y
where (name = 'apple' and the_sum >= 10) OR
  (name = 'banana' and the_sum >= 15) OR
  (name = 'carrot' and the_sum >= 5)

It seems your sample data has changed, please try this. I thought the ID doesnt have to follow tableA/tableB's id and the id is auto-generated as per the results.

Would be nice if you have another table that sets the threshold per name

MySQL JOINING same table returns wrong result on SUM()

try to sum amounts independently

select a.*, s.price send, t.price receive
from clients a 
left join (
  select sender_client_id, sum(price) price
  from orders
  group by sender_client_id
) s on a.id = s.sender_client_id
left join (
  select receiver_client_id, sum(price) price
  from orders
  group by receiver_client_id
) t on a.id = t.receiver_client_id

---

Related Topics