larksys
asked on
need sql to sum across columns
I am trying the following code using SQL and CF8;
<cffunction name="GetStatsRSFsy" access="public" returntype="query">
<cfargument name="MonthRange1" type="string" required="yes">
<cfargument name="MonthRange2" type="string" required="yes">
<cfargument name="User" type="string" required="yes">
<cfset qsfsy = "">
<!--- Month-to-date stat queries RSF stats monthly--->
<cfquery datasource="taxsearchdb" name="qsfsm">
SELECT
SUM(Fills) as sffillsm
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #MonthRange1# AND #MonthRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
SUM(InvoiceAmount1,Invoice
where InvoiceDate <= #MonthRange2#
<!---SUM(InvoiceAmount1,In
WHERE InvoiceDate between #MonthRange1# and #MonthRange2# --->
</cfquery>
<cfreturn qsfsm>
</cffunction>
The invoice amounts and dates are in the Jorder table. It gets a little more complicated in that invoices dated prior to the current month are considered "recurring production" and obviously invoices in the current month are "new production".
<cffunction name="GetStatsRSFsy" access="public" returntype="query">
<cfargument name="MonthRange1" type="string" required="yes">
<cfargument name="MonthRange2" type="string" required="yes">
<cfargument name="User" type="string" required="yes">
<cfset qsfsy = "">
<!--- Month-to-date stat queries RSF stats monthly--->
<cfquery datasource="taxsearchdb" name="qsfsm">
SELECT
SUM(Fills) as sffillsm
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #MonthRange1# AND #MonthRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
SUM(InvoiceAmount1,Invoice
where InvoiceDate <= #MonthRange2#
<!---SUM(InvoiceAmount1,In
WHERE InvoiceDate between #MonthRange1# and #MonthRange2# --->
</cfquery>
<cfreturn qsfsm>
</cffunction>
The invoice amounts and dates are in the Jorder table. It gets a little more complicated in that invoices dated prior to the current month are considered "recurring production" and obviously invoices in the current month are "new production".
ASKER
Invalid syntax near keyword SUM; (the second SUM)
SELECT
SUM(Fills) as sffillsy
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
SUM(InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9) as rcproductystat
where InvoiceDate <= #YearRange2#
SELECT
SUM(Fills) as sffillsy
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
SUM(InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9) as rcproductystat
where InvoiceDate <= #YearRange2#
Place all of the items you are selecting for display in one continuous sequence, e.g.:
SELECT
SUM(Fills) as sffillsy,
SUM(
CASE WHEN InvoiceDate <= #YearRange2# THEN
InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9
ELSE 0 END) as rcproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
REMEMBER: One SELECT selects all of the records being referenced in one swell foop. If you need to have multiple SELECT statements run, then run multiple SELECTs and combine the results
SELECT
SUM(Fills) as sffillsy,
SUM(
CASE WHEN InvoiceDate <= #YearRange2# THEN
InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9
ELSE 0 END) as rcproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
REMEMBER: One SELECT selects all of the records being referenced in one swell foop. If you need to have multiple SELECT statements run, then run multiple SELECTs and combine the results
That would not sum anything outside of YearRange1 and 2
try this instead
SELECT
SUM(CASE WHEN Date >= #YearRange1# Then Fills else 0 END) as sffillsy,
ISNULL(SUM(InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9),0) as rcproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date <= #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
try this instead
SELECT
SUM(CASE WHEN Date >= #YearRange1# Then Fills else 0 END) as sffillsy,
ISNULL(SUM(InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9),0) as rcproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date <= #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
ASKER
The following code;
SELECT
SUM(Fills) as sffillsy
SUM(
CASE WHEN InvoiceDate <= #YearRange2# THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat
SUM(
CASE WHEN InvoiceDate BETWEEN #yearRange1# and #YearRange2# THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
--------------------------
yielded a syntax error near the keyword SUM and the following SQL statement;
SELECT SUM(Fills) as sffillsy SUM( CASE WHEN InvoiceDate <= 20071131 THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat SUM( CASE WHEN InvoiceDate BETWEEN 20070101 and 20071131 THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat FROM JobBoardStats LEFT JOIN JOrder ON JobBoardStats.JobOrder=JOr
--------------------------
SELECT
SUM(Fills) as sffillsy
SUM(
CASE WHEN InvoiceDate <= #YearRange2# THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat
SUM(
CASE WHEN InvoiceDate BETWEEN #yearRange1# and #YearRange2# THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
--------------------------
yielded a syntax error near the keyword SUM and the following SQL statement;
SELECT SUM(Fills) as sffillsy SUM( CASE WHEN InvoiceDate <= 20071131 THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat SUM( CASE WHEN InvoiceDate BETWEEN 20070101 and 20071131 THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat FROM JobBoardStats LEFT JOIN JOrder ON JobBoardStats.JobOrder=JOr
--------------------------
larksys, please give my query (ID:20378286) a try
You need single quotes around the dates. You also need a comma after sffillsy. Try:
SELECT
SUM(Fills) as sffillsy,
SUM(
CASE WHEN InvoiceDate <= '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat
SUM(
CASE WHEN InvoiceDate BETWEEN '#yearRange1#' and '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between '#YearRange1#' AND '#YearRange2#' AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
SELECT
SUM(Fills) as sffillsy,
SUM(
CASE WHEN InvoiceDate <= '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat
SUM(
CASE WHEN InvoiceDate BETWEEN '#yearRange1#' and '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between '#YearRange1#' AND '#YearRange2#' AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
ASKER
Incorrect syntax near the keyword SUM;
SELECT
SUM(Fills) as sffillsy,
SUM(
CASE WHEN InvoiceDate1 <= '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat,
SUM(
CASE WHEN InvoiceDate1 BETWEEN '#yearRange1#' and '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
SELECT
SUM(Fills) as sffillsy,
SUM(
CASE WHEN InvoiceDate1 <= '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat,
SUM(
CASE WHEN InvoiceDate1 BETWEEN '#yearRange1#' and '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
am i being ignored?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The data type for JobBoardStats.Date is nvarchar. The data type for Jorder.InvoiceDate1 is decimal.
I need to sum all JobBoardStats.Fills ytd. In fact, I need to select all rows from JobBoardStats that match the date criteria and user and record types. From those records, if there is a JobOrderNumber, I need to join to Jorder and sum the InvoiceAmounts columns based on the InvoiceDates.
I hope that makes it a little clearer.
I need to sum all JobBoardStats.Fills ytd. In fact, I need to select all rows from JobBoardStats that match the date criteria and user and record types. From those records, if there is a JobOrderNumber, I need to join to Jorder and sum the InvoiceAmounts columns based on the InvoiceDates.
I hope that makes it a little clearer.
I gave you the most correct answer. Not only do you ignore me but you still haven't correctly defined your requirement. What if you decide to come up with more information later?
Your query clearly has an intention to get the "invoices dated prior to the current month" as well, which the previous answers have failed to address. Your latest comment contradicts that "that match the date criteria (only)" or was not worded very well.
Bye
Your query clearly has an intention to get the "invoices dated prior to the current month" as well, which the previous answers have failed to address. Your latest comment contradicts that "that match the date criteria (only)" or was not worded very well.
Bye
ASKER
The following code passes syntax muster but does not yield the correct amounts;
SELECT
SUM(Fills) as sffillsy,
SUM(
CASE WHEN InvoiceDate1 <= '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat,
SUM(
CASE WHEN InvoiceDate1 BETWEEN '#yearRange1#' and '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between '#YearRange1#' AND '#YearRange2#' AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
For one job record the invoice amounts are 41250 and 61875. For the other job record the invoice amounts are 40626 and 35874. The results for each of the job records is 17962. All invoice fields either have the amounts I stated or have a zero. I think I need to take another tack. I thought maybe I could do it in one SQL statement.
SELECT
SUM(Fills) as sffillsy,
SUM(
CASE WHEN InvoiceDate1 <= '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat,
SUM(
CASE WHEN InvoiceDate1 BETWEEN '#yearRange1#' and '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat
FROM JobBoardStats
LEFT JOIN JOrder
ON JobBoardStats.JobOrder=JOr
where Date between '#YearRange1#' AND '#YearRange2#' AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
For one job record the invoice amounts are 41250 and 61875. For the other job record the invoice amounts are 40626 and 35874. The results for each of the job records is 17962. All invoice fields either have the amounts I stated or have a zero. I think I need to take another tack. I thought maybe I could do it in one SQL statement.
ASKER
I apologize to imitchie. If you look at all the code I supplied, I have really not followed my own problem very well. I have several functions where I intend to gather stats for the month and goal predictions for the month and the same for the quarter and the same for the year. In general, all of that code works well. In one instance, where I am gathering "fills" for the month, quarter, and year, I need to examine the job associated with that record and determine production dollars from invoicing. Unfortuantely there are 9 possible invoices and dates that need to be examined. The test code I have been showing you doesn't even consider the complication of having 9 different invoices and dates on one record. I was trying to learn how to conditionally sum columns. As far as my initial question goes, bhess1 followed it through. However, and obviously, I still need help.
SUM(InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9)