CODEWITHSUNDEEP
mysqlsql
user1246800
user1246800

Reputation: 287

Calculate Sum() but excluding JOIN

In the sales table, there is a point field. I want to sum(point) when grouping by sales.submit_date but that wont add up correctly because it will duplicate the records from JOIN

SELECT 
    COUNT(DISTINCT sales.sales_id) as TotalSales, 
    COUNT(DISTINCT sales_lines.id) as TotalLiness
FROM `sales` 
    JOIN sales_lines ON sales_lines.sales_id = sales.sales_id
GROUP BY sales.submit_date

SQL above, this will count the number of sales in the sales table and also count number of lines in the sales_lines (number of lines matched to sales_lines.sales_id = sales.sales_id). This seem to work fine.

How do I sum(`sales.point') in the sales only?

Upvotes: 0

Views: 76

Answers (1)

Andrew
Andrew

Reputation: 8703

You could aggregate sales_lines up to the sales grain.

SELECT
  S.submit.date,
  ,sum(s.point)
  ,COUNT(s.sales_id) as TotalSales
  ,SUM(SL.SalesLines) as TotalLines
FROM
 sales S
INNER JOIN 
(Select
  sales_id
  ,count(distinct id) as SalesLines
 FROM
   sales_lines
 GROUP BY
  sales_id) SL
ON S.sales_id = SL.sales_id3
GROUP BY
 s.submit_date

Upvotes: 1

Related Questions