Using Group By Clause in Stored procedure

Question

hi guys, I have a storedprocedure in which iam getting InvoiceValue as one field.I have different invoice values corresponding to 1 fileid.I want to get the sum of invoice values corresponding to each fileid.How can i use group by here? here is my stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[rptGGM]
(
    @FromDate varchar(50),
    @ToDate varchar(50),
    @PartyID int,
    @WID int

)

As
DECLARE @WhereStr VarChar(800)

SET @FromDate = LTRIM(RTRIM(@FromDate)) 
SET @ToDate = LTRIM(RTRIM(@ToDate))

SET @WhereStr = 
    CASE @WID
     WHEN 23 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+'''))'
     WHEN 7 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+''')) AND  A.WorkID='+LTRIM(str(@WID))
     WHEN 6 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+''')) AND  A.WorkID='+LTRIM(str(@WID))
     WHEN 11 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+''')) AND  A.WorkID='+LTRIM(str(@WID))
     WHEN 12 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+'''))  AND  A.WorkID='+LTRIM(str(@WID))

    END


BEGIN
exec

('SELECT 
         A.FileNumber As [File_No],
      H.IDFNumber as [IDF No],
      H.IDFRegNo as [TZDAR],
         F.POString As [PO_Numbers],
      B.PartyName As [Exporter Name],
         F.SIString As [Supplier Invoices],
      C.CargoDesc As [Cargo_Description],
      dbo.PackCntDetails(A.FileID) As [Pk/Cnt_Details],
         D.VesselName As [Vessel Name],
         D.VoyageNo As [Voyage No],
      L.Amount As [Invoice Value],
      convert(varchar, Dbo.ActualDate(A.ETA), 103) + '' '' + convert(varchar, Dbo.ActualDate(A.ETA), 108) As ETA,
         convert(varchar,Dbo.ActualDate(D.ATASea),103) + '' '' + convert(varchar, Dbo.ActualDate(D.ATASea), 108) As ATA,
      convert(varchar,Dbo.ActualDate(H.PCVRIssuedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(H.PCVRIssuedDate), 108) as [PCVR Issued Date],
      convert(varchar,Dbo.ActualDate(V.VATRelDate),103) + '' '' + convert(varchar, Dbo.ActualDate(V.VATRelDate), 108) as [VR Requested],
      convert(varchar,Dbo.ActualDate(V.OriginalRecdDate),103) + '' '' + convert(varchar, Dbo.ActualDate(V.OriginalRecdDate), 108) as [VR Granted],
      convert(varchar,Dbo.ActualDate(H.SBECVRAppReturnedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(H.SBECVRAppReturnedDate), 108) as [SBE _VR App. ReturnedDate],
      convert(varchar,Dbo.ActualDate(H.SBECVRIssuedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(H.SBECVRIssuedDate), 108) as [SBE_CVR IssuedDate],
      convert(varchar,Dbo.ActualDate(I.CDFLodgedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(I.CDFLodgedDate), 108) as [CDF LodgedDate],
      convert(varchar,dbo.ActualDate(I.AssessmentPaidDate), 103) + '' '' + convert(varchar,dbo.ActualDate(I.AssessmentPaidDate),108) As [DutyPaidDate], 
      convert(varchar,Dbo.ActualDate(I.EntryLodgedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(I.EntryLodgedDate), 108) as [Entry LodgedDate],
      convert(varchar,Dbo.ActualDate(I.EntryPassedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(I.EntryPassedDate), 108) as [Custom Released],
      [Actual Days from ATA to CR]=DATEDIFF(dd, Dbo.ActualDate(D.ATASea),Dbo.ActualDate(I.EntryPassedDate)),
      convert(varchar,Dbo.ActualDate(K.ShippingLineReleaseDate),103) + '' '' + convert(varchar, Dbo.ActualDate(K.ShippingLineReleaseDate),108) as [ShippingLine ReleaseDate],
      convert(varchar,Dbo.ActualDate(K.PortInvoicePaidDate),103) + '' '' + convert(varchar, Dbo.ActualDate(K.PortInvoicePaidDate),108) as [Port_Charges Paid_Date],
      convert(varchar,Dbo.ActualDate(K.RemPortYardDateSea),103) + '' '' + convert(varchar, Dbo.ActualDate(K.RemPortYardDateSea),108) as [Rem_Sea_Port to Yard_Date],
      [Actual Days From CR to RFD Dar]=DATEDIFF(dd,Dbo.ActualDate(I.EntryPassedDate), Dbo.ActualDate(K.RemPortYardDateSea)),
      [Total No: of Days(ATA-RFD)]=DATEDIFF(dd,Dbo.ActualDate(D.ATASea), Dbo.ActualDate(K.RemPortYardDateSea)),
      A.Remarks As [File Status]
FROM
    FileMain A
INNER JOIN Party G ON G.PartyID = A.PartyID
LEFT JOIN Party B ON B.PartyID = A.ExporterID
LEFT JOIN Cargo C ON C.FileID = A.FileID
LEFT JOIN FileSea D ON D.FileID = A.FileID
LEFT JOIN SIPOString F ON F.FileID=A.FileID
LEFT JOIN IDFMain H ON H.FileID=A.FileID
LEFT JOIN Customs I ON I.FileID=A.FileID
LEFT JOIN VATRelief V ON V.FileID=A.FileID
LEFT JOIN PortAirPortOperation K ON K.FileID=A.FileID
LEFT JOIN Invoice L ON L.FileID=A.FileID and L.Incoterm=''CIF''' +@WhereStr)

END

Answer 1

The quickest implementation here would be to not use a GROUP BY. I would instead use a SubQuery within your SELECT to perform the SUM of the invoices.

Remove the LEFT JOIN to Invoice and then relace the L.Amount line in the SELECT with a SubQuery as follows

(SELECT SUM(Amount) FROM Invoice WHERE Invoice.FileID = A.FileID and Invoice.Incoterm = 'CIF') [Invoice Value]

If you wish to use a GROUP BY to perform the SUM then you either need to GROUP on all your other columns you SELECT, or perform the GROUP by within a VIEW (probably nested).