• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6210
  • Last Modified:

SQL sum across columns

Is it allowed to sum across the same columns twice in the same query? I get the correct result for the first sum but the second gives me 17962. That number makes no sense at all. While I was testing the code I was getting 17962 for both results.

            SELECT
            SUM(Fills) as sffillsy,
         SUM(
CASE WHEN ISNULL(InvoiceDate1, 0) BETWEEN '#yearRange1#'  AND '#YearRange2#' THEN ISNULL(InvoiceAmount1, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate2, 0) BETWEEN '#yearRange1#'  AND '#YearRange2#' THEN ISNULL(InvoiceAmount2, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate3, 0) BETWEEN '#yearRange1#'  AND '#YearRange2#' THEN ISNULL(InvoiceAmount3, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate4, 0) BETWEEN '#yearRange1#'  AND '#YearRange2#' THEN ISNULL(InvoiceAmount4, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate5, 0) BETWEEN '#yearRange1#'  AND '#YearRange2#' THEN ISNULL(InvoiceAmount5, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate6, 0) BETWEEN '#yearRange1#'  AND '#YearRange2#' THEN ISNULL(InvoiceAmount6, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate7, 0) BETWEEN '#yearRange1#'  AND '#YearRange2#' THEN ISNULL(InvoiceAmount7, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate8, 0) BETWEEN '#yearRange1#'  AND '#YearRange2#' THEN ISNULL(InvoiceAmount8, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate9, 0) BETWEEN '#yearRange1#'  AND '#YearRange2#' THEN ISNULL(InvoiceAmount9, 0) ELSE 0 END) as nwproductystat,
         SUM(
CASE WHEN ISNULL(InvoiceDate1, 0) <= '#YearRange2#' THEN ISNULL(InvoiceAmount1, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate2, 0) <= '#YearRange2#' THEN ISNULL(InvoiceAmount2, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate3, 0) <= '#YearRange2#' THEN ISNULL(InvoiceAmount3, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate4, 0) <= '#YearRange2#' THEN ISNULL(InvoiceAmount4, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate5, 0) <= '#YearRange2#' THEN ISNULL(InvoiceAmount5, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate6, 0) <= '#YearRange2#' THEN ISNULL(InvoiceAmount6, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate7, 0) <= '#YearRange2#' THEN ISNULL(InvoiceAmount7, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate8, 0) <= '#YearRange2#' THEN ISNULL(InvoiceAmount8, 0) ELSE 0 END +
CASE WHEN ISNULL(InvoiceDate9, 0) <= '#YearRange2#' THEN ISNULL(InvoiceAmount9, 0) ELSE 0 END) as rcproductystat
            FROM JobBoardStats
            LEFT JOIN JOrder as J
            ON JobBoardStats.JobOrder=J.JobOrderNumber          
            where Date between '#YearRange1#' AND '#YearRange2#' AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'

Open in new window

0
larksys
Asked:
larksys
1 Solution
 
multithreadingCommented:
Yes, the same column can appear in as many aggregates, formulas, whatever as you want.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now