CODEWITHSUNDEEP
sql-serversql-server-2008countsumaggregate
thevan
thevan

Reputation: 10344

How to use Count and Sum in the Same Query?

I have written the following query using the Aggregate function "SUM".

SELECT 
       D.PODetailID, 
       SUM(D.AcceptedQty) 
FROM STR_MRVDetail D 
INNER JOIN STR_MRVHeader H ON H.MRVID = D.MRVID 
INNER JOIN PUR_POHeader PH ON PH.POID = H.POID 
INNER JOIN PUR_PODetail PD ON PD.PODetailID = D.PODetailID
WHERE H.StatusID = 4
AND PH.POID = 839
AND (SELECT 
           SUM(AcceptedQty) 
     FROM 
           STR_MRVDetail 
     WHERE 
           STR_MRVDetail.PODetailID = PD.PODetailID) = 
                              (SELECT POQuantity FROM PUR_PODetail 
                               WHERE PUR_PODetail.PODetailID = PD.PODetailID)
                               GROUP BY D.PODetailID

Currently this Query returns 2 rows. I want to retrieve the count of this query. How to count the rows of the above query?

Upvotes: 0

Views: 4397

Answers (2)

Chris Gessler
Chris Gessler

Reputation: 23113

You can add @@ROWCOUNT to the query, which also gives you the final result set as well as the count of all the rows in the result set.

SELECT D.PODetailID, SUM(D.AcceptedQty), @@ROWCOUNT FROM STR_MRVDetail D  
INNER JOIN STR_MRVHeader H ON H.MRVID = D.MRVID  
INNER JOIN PUR_POHeader PH ON PH.POID = H.POID  
INNER JOIN PUR_PODetail PD ON PD.PODetailID = D.PODetailID 
WHERE H.StatusID = 4 
AND PH.POID = 839 
AND (SELECT SUM(AcceptedQty) FROM STR_MRVDetail  
     WHERE STR_MRVDetail.PODetailID = PD.PODetailID) =  
     (SELECT POQuantity FROM PUR_PODetail  
      WHERE PUR_PODetail.PODetailID = PD.PODetailID) 
GROUP BY D.PODetailID

Upvotes: 2

Abdul Ahad
Abdul Ahad

Reputation: 2221

SELECT COUNT(*) as Total_Rows
FROM
(
SELECT D.PODetailID as PODetailID, SUM(D.AcceptedQty) as Total_AcceptedQty
FROM STR_MRVDetail D 
INNER JOIN STR_MRVHeader H ON H.MRVID = D.MRVID 
INNER JOIN PUR_POHeader PH ON PH.POID = H.POID 
INNER JOIN PUR_PODetail PD ON PD.PODetailID = D.PODetailID
WHERE H.StatusID = 4
AND PH.POID = 839
AND (SELECT SUM(AcceptedQty) FROM STR_MRVDetail 
     WHERE STR_MRVDetail.PODetailID = PD.PODetailID) = 
     (SELECT POQuantity FROM PUR_PODetail 
      WHERE PUR_PODetail.PODetailID = PD.PODetailID)
GROUP BY D.PODetailID
) as t

Upvotes: 1

Related Questions