CODEWITHSUNDEEP
mysqljoin
Sirkong
Sirkong

Reputation: 550

MySQL Count and SUM from second table with group by

I'm trying to get sales and quantity sale by crossing two tables, group by the first one and sum from the second one.

First table has sales/operations: id_sales, sales_rep Second table has sales details: id_sales_details, id_sales, quantity

What I need to know is how many operations had each sales_rep and what was the total quantity sum of all those sales.

This MySQL query gives me the first part:

SELECT sales.sales_rep, count(*) AS sales
from sales
Group by sales_rep
Order by sales DESC

What I cannot solve is how to add to that query the second part I need. The result should look something like:

sales_rep    sales    quantity
Claire             4          13
Peter              2          18
Mary              1           8
John              1           7

Here's a Fiddle to make things clearer: http://sqlfiddle.com/#!9/708234/5

Upvotes: 0

Views: 177

Answers (2)

user3317519
user3317519

Reputation: 78

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;

Upvotes: 2

Dr. X
Dr. X

Reputation: 2930

Quick solution

SELECT w.sales_rep, w.sales, SUM(quantity) as quantity
FROM
(SELECT s.sales_rep, t.sales,d.quantity FROM sales AS s
INNER JOIN  sales_details AS d ON s.id_sales = d.id_sales
INNER JOIN  
  (SELECT sales_rep, count(*) AS sales
   from sales
   Group by sales_rep
   Order by sales DESC ) AS t 
  ON s.sales_rep = t.sales_rep) AS w

  GROUP BY w.sales_rep, w.sales
  ORDER BY w.sales_rep ASC

Upvotes: 0

Related Questions