13

I have a query where I am only selecting the TOP 10 rows, but I have a SUM function in there that is still taking the sum of all the rows (disregarding the TOP 10). How do I get the total of only the top 10 rows? Here is my SUM function :

SUM( fact.Purchase_Total_Amount) Total
Cfw412
  • 131
  • 1
  • 1
  • 3
  • 1
    The answer to this is going to be dependent on which DBMS you use (e.g., in SQL Server you would use `TOP`, in MySQL `LIMIT`, in Oracle `ROWNUM` or the `ROW_NUMBER()` window function). Perhaps you might copy and paste the query you're using to get the top 10 rows? – David Faber Feb 12 '15 at 14:31
  • Sorry, this is SQL server. This is the first part of the query: `SELECT top 10 itmf.Table_Name ,SUM( fact.Purchase_Total_Amount) Total ,(SUM(fact.Purchase_Total_Amount)/ (SELECT SUM(Purchase_Total_Amount) FROM TableName fact join TableName2 .....` – Cfw412 Feb 12 '15 at 16:33

2 Answers2

25

Have you tried to use something like this:

SELECT SUM(Whatever)
FROM (
    SELECT TOP(10) Whatever
    FROM TableName
) AS T
Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • So the TOP 10 statement should be within the nested query? inside the nested query is my function for calculating percentage and that didn't seem to change anything – Cfw412 Feb 12 '15 at 16:38
  • Have you tried? As far as i can see (in your comments), you do that in a different way. – Maciej Los Feb 12 '15 at 18:42
  • Note that the "AS T" is not an optional part of the syntax (at least in MSSQL) – Eliot Gillum Aug 14 '21 at 09:33
3

Use the TOP feature with a nested query

SELECT SUM(innerTable.Purchase_Total_Amount) FROM
(SELECT TOP 10 Purchase_Total_Amount FROM Table) as innerTable
LCIII
  • 3,102
  • 3
  • 26
  • 43