CODEWITHSUNDEEP
mysqlsqlgroup-bysum
RSantos
RSantos

Reputation: 21

GROUP BY Causing Issues with SUM()

I'm trying to run a sales report query.

The total I am receiving buy utilizing SUM() is not correct: it seems that the issue could be with my GROUP BY.

The query:

SELECT salesreport.canprevid,
  storelist.organization,
  SUM(quantity * unitprice * (100 - discount) / 100) AS total, 
  storelist.external_manager,
  storelist.manager
  FROM storelist
  INNER JOIN salesreport
    ON storelist.store_id = salesreport.canprevid
  WHERE salesreport.date >= "2016-01-01"
    AND salesreport.date <= "2016-01-31"
  GROUP BY canprevid
  ORDER BY organization DESC;

As expected, I am able to pull:

|canprevid|organization|total (for the month of january 2016)| external manager| manager

However, when I do a grand total of total, using SUM(), total does not equal what is the true total amount for the month of, say January 2016.

I'm not sure why some records would be removed or if GROUP BY is causing issues with the calculations.

Upvotes: 0

Views: 696

Answers (1)

SQLSuperHero
SQLSuperHero

Reputation: 618

Add storelist.organization to your GROUP BY:

SELECT salesreport.canprevid,
  storelist.organization,
  SUM(quantity * unitprice * (100 - discount) / 100) AS total, 
  storelist.external_manager,
  storelist.manager
  FROM storelist
  INNER JOIN salesreport
    ON storelist.store_id = salesreport.canprevid 
  WHERE salesreport.date >= "2016-01-01"
    AND salesreport.date <= "2016-01-31"
  GROUP BY salesreport.canprevid, storelist.organization
  ORDER BY storelist.organization DESC;

Upvotes: 1

Related Questions