Solved

need sql to sum across columns

Posted on 2007-11-29
14
505 Views
Last Modified: 2010-08-05
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=JOrder.JobOrderNumber            
            where Date between #MonthRange1# AND #MonthRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
            SUM(InvoiceAmount1,InvoiceAmount2,InvoiceAmount3,InvoiceAmount4,InvoiceAmount5,InvoiceAmount6,InvoiceAmount7,InvoiceAmount8,InvoiceAmount9,) as rcproductmstat
            where InvoiceDate <= #MonthRange2#
            <!---SUM(InvoiceAmount1,InvoiceAmount2,InvoiceAmount3,InvoiceAmount4,InvoiceAmount5,InvoiceAmount6,InvoiceAmount7,InvoiceAmount8,InvoiceAmount9,) as nwproductmstat
             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".


0
Comment
Question by:larksys
  • 6
  • 4
  • 4
14 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 20377513
Replace the commas with plus signs, and you should be close:

SUM(InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9)
0
 
LVL 1

Author Comment

by:larksys
ID: 20377815
Invalid syntax near keyword SUM;  (the second SUM)            

SELECT
            SUM(Fills) as sffillsy

            FROM JobBoardStats
            LEFT JOIN JOrder
            ON JobBoardStats.JobOrder=JOrder.JobOrderNumber            
            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#
0
 
LVL 32

Expert Comment

by:bhess1
ID: 20377998
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=JOrder.JobOrderNumber            
            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
0
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
LVL 25

Expert Comment

by:imitchie
ID: 20378286
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=JOrder.JobOrderNumber            
            where Date <= #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
0
 
LVL 1

Author Comment

by:larksys
ID: 20378404
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=JOrder.JobOrderNumber            
            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=JOrder.JobOrderNumber where Date between 20070101 AND 20071131 AND ConsRec = 'VL ' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
--------------------------------------------------------------------------------------------------------------------------
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20378431
larksys, please give my query (ID:20378286) a try
0
 
LVL 32

Expert Comment

by:bhess1
ID: 20378442
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=JOrder.JobOrderNumber            
            where Date between '#YearRange1#' AND '#YearRange2#' AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
0
 
LVL 1

Author Comment

by:larksys
ID: 20378661
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=JOrder.JobOrderNumber            
            where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20378708
am i being ignored?
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 20379291
Still missing single quotes around the dates in the WHERE line.

Take the generated SQL code, and run it in Query Analyzer.  That should give you better visibility to the issue.
0
 
LVL 1

Author Comment

by:larksys
ID: 20379982
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.

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20380030
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
0
 
LVL 1

Author Comment

by:larksys
ID: 20380103
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=JOrder.JobOrderNumber          
            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.
0
 
LVL 1

Author Comment

by:larksys
ID: 20380147
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.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question