Show total for every unique Id

• gaurav

  SSC-Addicted

  Points: 404

  More actions

  June 17, 2022 at 3:30 am

  #4050727

  Hello Everyone,

  In my select query with aggregate function I want to show total amount for every location.

  I wrote the query like below

  SELECT TLD.location_id, TP.Location, ISNULL(SUM(CAST(TLD.land_rate AS DECIMAL)),0) AS TotalRate --SUM(CAST(TLD.land_area AS DECIMAL)) As TotalArea
  FROM tblLandDetails TLD
  LEFT JOIN tbl_Projects TP ON TP.Sno = TLD.location_id
  WHERE TP.STATUS = 1
  AND TLD.lease_from >= CAST(DATEADD(MONTH, - 11, GETDATE()) AS DATE)
  AND TLD.payment_by = 'L'
  AND TP.status = 1
  AND TLD.location_id IN (
  SELECT TP.Sno
  FROM tbl_Projects TP
  JOIN tblProjectAssign TPA ON TP.Sno = TPA.project_id
  WHERE TPA.user_name = 'vsarraf'
  )
  GROUP BY TLD.location_id, TP.Location,TLD.renewal_date
  ORDER BY TLD.renewal_date

  The output of above query is as below

  

  See highlighted ids. I want to sum for duplicate ids. For example for id 97 it should show 180000 (48000+12000+12000+108000).

  Kindly suggest

  • This topic was modified 2 years, 5 months ago by  gaurav.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  June 17, 2022 at 7:15 am

  #4050821

  SELECT Location_ID, Location, SUM(TotalRate)
  FROM <tableName>
  GROUP BY Location_ID, Location;

• Phil Parkin

  SSC Guru

  Points: 246963

  More actions

  June 17, 2022 at 7:31 am

  #4050826

  I suspect your GROUP BY is causing the issue. Try changing it to

  GROUP BY TLD.location_id, TP.Location

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• kaj

  SSCommitted

  Points: 1605

  More actions

  June 20, 2022 at 11:00 pm

  #4052439

  Alternatively you'll need to use a window function instead of a conventional group by.