Show total for every unique Id

Question

Post reply

Show total for every unique Id

gaurav

SSC-Addicted

Points: 404

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

Capture

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 1 year, 10 months ago by gaurav.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 2

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

GROUP BY TLD.location_id, TP.Location

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

kaj SSCommitted Points: 1580 More actions · Answer 3

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