Solved

need sql to sum across columns

Posted on 2007-11-29
14
511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
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:Brendt Hess
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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:Brendt Hess
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:
Brendt Hess 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…

695 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