CODEWITHSUNDEEP
sql
Brasciole
Brasciole

Reputation: 377

Calculate the SUM of a SUM

I'm struggling with calculating the SUM on a Query that has a SUM. Here's my query:

SELECT rs.resellerid
       ,r.company
       ,r.insidesales
       ,SUM(total_reseller_sales) as TotalResellerSalesYear
  FROM sales_report_resellers rs
    INNER JOIN resellers r
      ON rs.resellerid = r.resellerid
  WHERE (sid > '282' AND sid < '292')
    AND r.insidesales = 1
  GROUP BY rs.resellerid, r.company, r.insidesales

The query returns 5 records with each a dollar amount. I need to the SUM of all 5 records.

Upvotes: 0

Views: 82

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Remove Group By and non aggregate columns from Select list 

SELECT  SUM(total_reseller_sales) as Total
FROM sales_report_resellers rs 
INNER JOIN resellers r 
ON rs.resellerid = r.resellerid 
WHERE (sid > '282' AND sid < '292') 
AND r.insidesales = 1

Upvotes: 1

Nate Anderson
Nate Anderson

Reputation: 690

Without seeing a sample dataset, I would guess you need to remove resellerid from the SELECT and GROUP BY.

SELECT
   [r].[company]
   ,[r].[insidesales]
   ,SUM([total_reseller_sales]) AS [TotalResellerSalesYear]
FROM
    [sales_report_resellers] [rs]
    INNER JOIN [resellers] [r] ON [rs].[resellerid] = [r].[resellerid]
WHERE
    (
     [sid] > '282'
     AND [sid] < '292'
    )
    AND [r].[insidesales] = 1
GROUP BY
   [r].[company]
   ,[r].[insidesales];

Upvotes: 0

Related Questions