MySQL Join Two Tables Count and Sum from Second Table

MySQL Join two tables count and sum from second table

You could use two sub-queries:

SELECT  a.*
      , (SELECT Count(b.id) FROM inquiries I1 WHERE I1.dealer_id = a.id) as counttotal
      , (SELECT SUM(b.cost) FROM inquiries I2 WHERE I2.dealer_id = a.id) as turnover
FROM dealers a
ORDER BY name ASC

Or

SELECT  a.*
     , COALESCE(T.counttotal, 0) as counttotal   -- use coalesce or equiv. to turn NULLs to 0
     , COALESCE(T.turnover, 0) as turnover       -- use coalesce or equiv. to turn NULLs to 0
 FROM dealers a
 LEFT OUTER JOIN (SELECT a.id, Count(b.id) as counttotal, SUM(b.cost) as turnover
               FROM dealers a1 
               INNER JOIN inquiries b ON a1.id = b.dealer_id
              GROUP BY a.id) T
         ON a.id = T.id
ORDER BY a.name

Join two tables, count the first table based on multiple conditions in the second table

You can use conditional aggregation to achieve the results you want:

SELECT c.id AS No,
       c.category,
       COUNT(CASE WHEN p.entry != '0000-00-00' AND p.verified = '0000-00-00' THEN 1 END) AS entry,
       COUNT(CASE WHEN p.verified != '0000-00-00' THEN 1 END) AS verified
FROM category c
LEFT JOIN product p ON p.category = c.id
GROUP BY c.id, c.category

Output:

No  category    entry   verified
1   food        1       1
2   drinks      2       0

Demo on dbfiddle

In MySQL you can simplify the COUNT to a SUM of the condition, since MySQL treats booleans as 1 or 0 in a numeric context:

SELECT c.id AS No,
       c.category,
       SUM(p.entry != '0000-00-00' AND p.verified = '0000-00-00') AS entry,
       SUM(p.verified != '0000-00-00') AS verified
FROM category c
LEFT JOIN product p ON p.category = c.id
GROUP BY c.id, c.category

The output is the same for this query. Demo on dbfiddle

MySQL Count and SUM from second table with group by

SELECT s.sales_rep, count(*) AS operations, sum(d.quantity)
  from sales s, sales_details d 
   where s.id_sales = d.id_sales 
   Group by s.sales_rep 
   Order by operations DESC;

Join two tables with SUM and COUNT

Use sub-queries like so:

select a.pn, a.loc, a.q, b.c from 
  (select h.pn, h.loc, sum(qty) q from history h group by h.pn, h.loc) a
  join
  (select r.pn, r.loc, count(sn) c from rota r group by r.pn, r.loc) b
  on a.pn = b.pn and a.loc = b.loc
  order by a.pn;

(tried it on Oracle, don't have MySQL available right now, but it should work fine or with minor adaptations)

SQL - How can I JOIN two tables and SUM a column based on IDs between them?

You can simply have a correlated sub-query that calculates the tbl2 sum:

select tbl1.name, 
       SUM(tbl1.total), 
       SUM(COALESCE((select SUM(tbl2.qty) 
                     from tbl2
                     where tbl1.id = tbl2.id), 0)) as qty_tot
from tbl1
GROUP by tbl1.name

mysql join two tables, count and sum alias

You can use a cross join to your same query by calculation sum of the count column

SELECT t1.*,t2.totalvoteCount FROM (
SELECT p. * , COUNT( r.poll_option_id ) AS voteCount
FROM poll_options AS p
LEFT JOIN poll_responses AS r ON ( r.poll_option_id = p.id ) 
WHERE p.poll_id =1
GROUP BY p.id
LIMIT 0 , 30
) t1
CROSS JOIN (
SELECT SUM(voteCount) totalvoteCount
FROM (
SELECT  p.id,COUNT( r.poll_option_id ) AS voteCount
FROM poll_options AS p
LEFT JOIN poll_responses AS r ON ( r.poll_option_id = p.id ) 
WHERE p.poll_id =1
GROUP BY p.id
LIMIT 0 , 30
) t
) t2

Output would be as follows

id poll_id caption voteCount   totalvoteCount 
 1  1       32          1        2
 2  1       256         1        2
 3  1       512         0        2

Mysql join two tables sum, where and group by

You are multiplying the payments amount with the number of sales records, because you are joining all payments records with all sales records before summing up the amounts.

Aggregate first, and only join then.

In case there can always only be one payments record per date, time and name:

select p.name, p.time, p.name, s.sales_total, p.amount
from payments p
join
(
  select date, time, name, sum(total) as total
  from sales
  group by date, time, name
) s
 on s.date = p.date and s.time = p.time and s.name = p.name
where p.date = date '2017-04-01';

Otherwise:

select p.name, p.time, p.name, s.total, p.amount
(
  select date, time, name, sum(amount) as amount
  from payments
  group by date, time, name
) p
join
(
  select date, time, name, sum(total) as total
  from sales
  group by date, time, name
) s
 on s.date = p.date and s.time = p.time and s.name = p.name
where p.date = date '2017-04-01';

MySQL join and COUNT() on multiple tables

You will need to use DISTINCT, but also you need to count the IDs, not the foreign keys:

SELECT
    table1.name,
    COUNT(DISTINCT table2.id) AS table2_count,
    COUNT(DISTINCT table3.id) AS table3_count,
    COUNT(DISTINCT table4.id) AS table4_count,
    SUM(table4.size) AS table4_size
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1_id
LEFT JOIN table3 ON table2.id = table3.table2_id
LEFT JOIN table4 ON table3.id = table4.table3_id
WHERE table1.id = 1

Here is a fiddle.

Explanation: The DISTINCT key word eliminates all duplicate values resulting in a list of unique values.

If you run your query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          1          200 
test  1          (null)     (null)     (null) 
test  1          (null)     (null)     (null) 

So if you add the COUNT() and SUM(), you obviously get:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224 

However, using DISTINCT with your query won't help because you can clearly see the duplicate values, which will result in:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1224 

Now, if you run my query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          2          200 
test  2          (null)     (null)     (null) 
test  3          (null)     (null)     (null) 

If you add the COUNT() and SUM(), you get exactly the same results like your query:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224 

However, because this time you have different values (i.e. not all are 1), so now if you count the unique values using DISTINCT, you get:

name  table1_id  table2_id  table3_id  size
test  3          1          2          1224 

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;

---

Related Topics