Solved

need sql to sum across columns

Posted on 2007-11-29
14
496 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now