Sql-server – How to get the count of one column with GROUP BY function in a INNER JOIN?

sql server

I want to get the total qty one person has got for one date (group by date I guess). How should I get it? This is the code I am working on.

SELECT  Dc.Name AS Market   
        ,R.Name AS Route    
        ,C.Id   
        ,C.ERPCusCode   
        ,C.Name 
        --,C.Address1 AS Address    
        ,(isnull(C.Address1,'')+' '+isnull(C.Address2,'')+' '+isnull(C.Address3,'')+' '+isnull(C.Address4,'')) AS Address
        ,C.CustomerGradeId AS volumeClass   
        ,I.ERPItemCode  
        ,H.InvoiceDate  
        ,D.Qty
FROM InvoiceHeader H
INNER JOIN InvoiceDetail D ON H.InvoiceNo=D.InvoiceNo
INNER JOIN ItemMaster I ON I.Id=D.ItemMasterId
INNER JOIN Customer C ON C.Id=H.CustomerId
INNER JOIN DistributionCenter Dc ON Dc.Id=H.DistributionCenterId
INNER JOIN Route R ON R.Id=H.RouteId

WHERE H.Deleted=0 AND H.InvoiceDate BETWEEN '2018/01/01' AND '2018/06/06'
AND C.Active=1

Best Answer

SUM(D.Qty) as Total_QTY

then use GROUP BY H.InvoiceDate and rest of your columns. Just be wary if your H.InvoiceDate is a DateTime, and not Date, results might not look like you want it to.