Firebird total column I have a query that I am struggling with. The problem I have is that I would like it to just display a total of column variancevalue as a result.

SELECT
  DEPARTMENT.DESCRIPTION AS DepartmentName,
  SUBDEPARTMENT.DESCRIPTION AS SubDeptName,
  SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,
  sum(STOCKTAKELIST.QTYONHAND) Onhand,
  sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
  sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
  sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
  STOCKTAKELIST STOCKTAKELIST
  INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
  INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
  LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBDEPARTMENT ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBSUBDEPARTMENT ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
WHERE
  STOCKTAKELIST.ListCreate &gt;= '01 Jun 2018' AND
  STOCKTAKELIST.ListCreate &lt;= '10 Jun 2018 23:59:59' AND
  STOCKTAKELIST.IncludeInCount = 'Yes' AND
  STOCKTAKELIST.STOCKCOUNT &lt;&gt; STOCKTAKELIST.QTYONHAND AND
  STOCKTAKELIST.Status = 'Complete'
GROUP BY
  1,2,3
ORDER BY DEPARTMENT.DESCRIPTION, SUBDEPARTMENT.DESCRIPTION, SUBSUBDEPARTMENT.DESCRIPTION


Can this be done and if so how would I do that?
If you want to replace the entire output of your current query with just one single-column row holding the total across the VarianceValue column of the original output, then just take the current query and remove


the GROUP BY clause,
the ORDER BY clause,
the left joins,
and all SELECT terms but one (the last one):


SELECT
  DEPARTMENT.DESCRIPTION AS DepartmentName,
  SUBDEPARTMENT.DESCRIPTION AS SubDeptName,
  SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,
  sum(STOCKTAKELIST.QTYONHAND) Onhand,
  sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
  sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
  sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
  STOCKTAKELIST STOCKTAKELIST
  INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
  INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
  LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBDEPARTMENT ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBSUBDEPARTMENT ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
WHERE
  STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
  STOCKTAKELIST.ListCreate &lt;= '10 Jun 2018 23:59:59' AND
  STOCKTAKELIST.IncludeInCount = 'Yes' AND
  STOCKTAKELIST.STOCKCOUNT &lt;> STOCKTAKELIST.QTYONHAND AND
  STOCKTAKELIST.Status = 'Complete'
GROUP BY
  1,2,3
ORDER BY DEPARTMENT.DESCRIPTION, SUBDEPARTMENT.DESCRIPTION, SUBSUBDEPARTMENT.DESCRIPTION


That is, without the parts struck through the query should look like this (just to make it absolutely unambiguous and copy-paste-friendly):

SELECT
  sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
  STOCKTAKELIST STOCKTAKELIST
  INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
  INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
WHERE
  STOCKTAKELIST.ListCreate &gt;= '01 Jun 2018' AND
  STOCKTAKELIST.ListCreate &lt;= '10 Jun 2018 23:59:59' AND
  STOCKTAKELIST.IncludeInCount = 'Yes' AND
  STOCKTAKELIST.STOCKCOUNT &lt;&gt; STOCKTAKELIST.QTYONHAND AND
  STOCKTAKELIST.Status = 'Complete';


It is quite possible that the inner joins are unnecessary too. They probably need to stay if they can affect the result. And they can affect the result if either of these is true:


STOCKTAKELIST.INTRECNO_STOCK is nullable and/or may contain values not found in STOCK.INTRECNO;
STOCK.INTRECNO_LOCATION is nullable and/or may contain values not found in LOCATION.INTRECNO.


If you have foreign key relationships in place, then what matters is just the nullability of STOCKTAKELIST.INTRECNO_STOCK and STOCK.INTRECNO_LOCATION. Now if both columns are not nullable and the referential integrity is guaranteed, then you should be able to get the same result by reducing the query to just this:

SELECT
  sum((STOCKCOUNT - QTYONHAND) * CostPrice) VarianceValue
FROM
  STOCKTAKELIST
WHERE
  ListCreate &gt;= '01 Jun 2018' AND
  ListCreate &lt;= '10 Jun 2018 23:59:59' AND
  IncludeInCount = 'Yes' AND
  STOCKCOUNT &lt;&gt; QTYONHAND AND
  Status = 'Complete';