CODEWITHSUNDEEP
mysqlsql
codeNinja
codeNinja

Reputation: 1462

prevent JOIN from matching same row over and over again

So i have 2 tables like this

trans_data table

mnth| id | units
2   | ab | 20
3   | cd | 20
2   | ab | 25
2   | fd | 28
2   | ab | 40
2   | cd | 70
3   | ab | 80
2   | ab | 10

quota table 

mnth | metric | id | quota
2    | 1      | ab | 30
2    | 1      | cd | 30
2    | 1      | fd | 30
3    | 1      | ab | 40
3    | 1      | cd | 40
3    | 1      | fd | 40

Here is my SQL

SELECT 
  SUM(trans_data.units) AS ga, SUM(quota.quota)
FROM
  trans_data 
  LEFT JOIN quota ON 
  trans_data.id = quota.id
  AND quota.mnth BETWEEN 2 AND 2 
  AND quota.metric = 1
WHERE trans_data.id = 'ab'    
  AND trans_data.mnth BETWEEN 2 AND 2

What is happening is that since there are multiple rows in the trans_data table that have a id='ab', each of those rows is getting paired with the one row in quota that has id='ab'.

This throws off the sum value. what can i do so that the rows from quota are not repeated in the sum() calculation

Desired Result:

sum(trans_data.units)   |  sum(Quota.quota)
183                     | 30

Upvotes: 0

Views: 66

Answers (2)

Vulcronos
Vulcronos

Reputation: 3456

You don't actually need to sum the quota in this case since there is only a singe row. You could change the sum function to a max function in this case. In general Barmar's solution is best since it will handle more than one row if that ever occurs.

Upvotes: 0

Barmar
Barmar

Reputation: 781370

You don't need an INNER JOIN, just calculate the two sums independently:

SELECT ga, total_quota
FROM (SELECT SUM(units) AS ga
      FROM trans_data
      WHERE id = 'ab'
      AND mnth BETWEEN 2 AND 2) AS t1
CROSS JOIN
     (SELECT SUM(quota) AS total_quota
      FROM quota
      WHERE id = 'ab'
      AND mnth BETWEEN 2 AND 2
      AND metric = 1) AS t2

Or:

SELECT
     (SELECT SUM(units)
      FROM trans_data
      WHERE id = 'ab'
      AND mnth BETWEEN 2 AND 2) AS ga,
     (SELECT SUM(quota)
      FROM quota
      WHERE id = 'ab'
      AND mnth BETWEEN 2 AND 2
      AND metric = 1) AS total_quota

Upvotes: 2

Related Questions