CODEWITHSUNDEEP
mysqlsqlgroup-bysum
d.k
d.k

Reputation: 4466

MySQL SUM does not work with GROUP BY

I have these tables

customers table

products table

purchases table

versions table

When I execute this query 

SELECT 
    products.name,
    versions.version, 
    purchases.`purchase date`, 
    SUM(purchases.`number of licenses`) 
FROM products,
    versions, 
    purchases 
WHERE 
    products.id = versions.`product id` 
    AND products.id = 1
GROUP BY
    products.name, 
    versions.version;

I get this result:

enter image description here

Where number of licences is the total sum of purchases in the table, when I expect only 20 (with product id = 1, that is rows 8, 10, 11) instead of 54.

Could someone help me with this, please?

Upvotes: 1

Views: 2165

Answers (1)

Jim Garrison
Jim Garrison

Reputation: 86774

Your where clause does not mention purchases so it's summing all purchase for every row.

You will be better off expressing the joins using modern JOIN ... ON syntax. Since you haven't provided table definitions it's hard to specify what you should do.

The following is a rough guess to show you the syntax. It will certainly not work as-is; you must tweak it for your situation.

SELECT 
    pr.name,
    v.version, 
    pu.`purchase date`, 
    SUM(pu.`number of licenses`) 
FROM products pr
    JOIN versions v on pr.id = v.`product id` 
    JOIN purchases pu 
       on pu.product_id=pr.product_id 
      and pu.product_version_id=v.version_id
GROUP BY
    pr.name, 
    v.version;

Upvotes: 2

Related Questions