CODEWITHSUNDEEP
sqldistinct
mediasurface
mediasurface

Reputation: 47

SQL sum only distinct value

I have a table which contain the following columns:

plantId (pk)
unitId
maxCapacity
numberOfPlants
geographicalArea
agreementStart
agreementEnd
plantType

The table contain the following data:

1, A1, 10, 1, 3, 2013-01-01, 2013-12-31
2, A2, 10, 1, 3, 2013-01-01, 2013-12-31
3, A1, 10, 1, 3, 2013-03-01, 2013-12-31

I would like to query my table like this:

SELECT DISTINCT 
  plantType, 
  geographicalArea, 
  sum(maxCapacity) as maxCapacity,   
  sum(numberOfPlants) as numberOfPlants, 
  count(unitId) as idCount 
FROM 
  tbl_plant 
WHERE 
  (agreementStart <= '2013-11-28' AND 
   agreementEnd >= '2013-11-28') AND 
  plantType <> '0' AND 
  plantType = '2' AND 
  geographicalArea = '3' 
GROUP BY 
  plantType, geographicalArea

This query works well as long as there is only one unique unitId returned per row in the resultset. However, if the same unitId is returned a second time (see A1 above), I do not want the sum(maxCapacity) and sum(numberOfPlants) include the sum a second time since it has already included it.

Any ideas of how I'd need to change this query?

Upvotes: 0

Views: 2309

Answers (1)

evhen14
evhen14

Reputation: 1927

Use inner query to get rid of duplicates

SELECT plantType, geographicalArea, sum(maxCapacity) as maxCapacity, sum(numberOfPlants) as numberOfPlants, count(unitId) as idCount 
  FROM tbl_plant 
 WHERE plantId in ( 
   SELECT MAX(plantId) 
     FROM tbl_plant 
    WHERE (agreementStart <= '2013-11-28' AND agreementEnd >= '2013-11-28') 
      AND plantType <> '0' 
      AND plantType = '2' 
      AND geographicalArea = '3' 
    GROUP BY unitId
)
GROUP BY plantType, geographicalArea

Upvotes: 1

Related Questions