Solved

Multiple Aggregate Functions in the Having Clause

Posted on 2014-10-29
7
81 Views
Last Modified: 2014-11-03
Hi All,

I'm farely new to writing sql code.
I have a query that I"m trying to currently write.
I'm trying to place 3 conditions in the having clause, but keep on getting an error.
"Incorrect Syntax near '<'.

Here are my conditions:
The total for all credits and debits does not equal 0.
sum of cr_amount 6 does not equal 0
sum of db_amount6 does not equal 0


Can someone help me with this.
Or any suggestions on how to write the code more efficently would be welcomed too.

select (rtrim(RIGHT(CONVERT(Char(5), gla.COMPANY + 10000), 4))+'-'+rtrim(RIGHT(CONVERT(Char(5), gla.ACCT_UNIT + 10000), 3))) AS ENTITY_UNIQUE_IDENTIFIER,
       (rtrim(RIGHT(CONVERT(Char(5), gla.ACCOUNT + 10000), 4)+'-'+ RIGHT(CONVERT(Char(5), gla.SUB_ACCOUNT + 10000), 4)))AS ACCOUNT_NUMBER,
       rtrim(RIGHT(CONVERT(Char(5), gla.ACCOUNT + 10000), 4)) AS NATURAL_ACCT,
       rtrim(RIGHT(CONVERT(Char(5), gla.SUB_ACCOUNT + 10000), 4)) AS SUB_ACCOUNT,
       rtrim(gls.ACCOUNT_DESC) AS GROUP_DESCRIPTION_LAWSON,
       '' AS SHORT_GROUP_DESC_LAWSON,
       '' AS SPECIALTY,
       '' AS BILLING_SYSTEM,
       '' AS KEY9,
       '' AS KEY10,
       rtrim(glc.ACCOUNT_DESC) as GROUP_DESCRIPTION_LAWSON,
       '' AS BANK_ACCOUNT_REFERENCE,
       'A' AS FINANCIAL_STATEMENT,
       '' AS ACCOUNT_TYPE,
       'TRUE' AS ACTIVE_ACCOUNT,
       'TRUE' AS ACTIVITY_IN_PERIOD,
       '' AS ALTERNATE_CURRENCY,
       'USD' AS ACCOUNT_CURRENCY,
       '6/30/2014' AS PERIOD_END_DATE,
       --convert(varchar(10),(dateadd(month, ((YEAR(GETDATE())-1900)*12)+Month(getdate()),-1)),101),
       '' AS GL_REPORTING_BALANCE,
       '' AS GL_ALTERNATE_BALANCE,
       
    (Sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01) +
     sum(gla.DB_AMOUNT_02) + sum(gla.CR_AMOUNT_02) + sum(gla.DB_AMOUNT_03) + sum(gla.CR_AMOUNT_03) + sum(gla.DB_AMOUNT_04) + sum(gla.CR_AMOUNT_04) +
     sum(gla.DB_AMOUNT_05) + sum(gla.CR_AMOUNT_05) + sum(gla.DB_AMOUNT_06) + sum(gla.CR_AMOUNT_06)) AS GL_ACCOUNT_BALANCE,

       sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) as BeginBalance,
       sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01) as Month1,
       sum(gla.DB_AMOUNT_02) + sum(gla.CR_AMOUNT_02) as Month2,
       sum(gla.DB_AMOUNT_03) + sum(gla.CR_AMOUNT_03) as Month3,
       sum(gla.DB_AMOUNT_04) + sum(gla.CR_AMOUNT_04) as Month4,
       sum(gla.DB_AMOUNT_05) + sum(gla.CR_AMOUNT_05) as Month5,
       sum(gla.DB_AMOUNT_06) + sum(gla.CR_AMOUNT_06) as Month6,
       sum(gla.DB_AMOUNT_07) + sum(gla.CR_AMOUNT_07) as Month7,
       sum(gla.DB_AMOUNT_08) + sum(gla.CR_AMOUNT_08) as Month8,
       sum(gla.DB_AMOUNT_09) + sum(gla.CR_AMOUNT_09) as Month9,
       sum(gla.DB_AMOUNT_10) + sum(gla.CR_AMOUNT_10) as Month10,
       sum(gla.DB_AMOUNT_11) + sum(gla.CR_AMOUNT_11) as Month11,
       sum(gla.DB_AMOUNT_12) + sum(gla.CR_AMOUNT_12) as Month12,
       
       sum(gla.DB_AMOUNT_01) AS DBMONTH1,
       sum(gla.CR_AMOUNT_01) as CRMonth1,
       sum(gla.DB_AMOUNT_02) AS DBMONTH2,
       sum(gla.CR_AMOUNT_02) as CRMonth2,
       sum(gla.DB_AMOUNT_03) AS DBMONTH3,
       sum(gla.CR_AMOUNT_03) as CRMonth3,
       sum(gla.DB_AMOUNT_04) AS DBMONTH4,
       sum(gla.CR_AMOUNT_04) as CRMonth4,
       sum(gla.DB_AMOUNT_05) AS DBMONTH5,
       sum(gla.CR_AMOUNT_05) as CRMonth5,
       sum(gla.DB_AMOUNT_06) AS DBMONTH6,
       sum(gla.CR_AMOUNT_06) as CRMonth6,
       sum(gla.DB_AMOUNT_07) AS DBMONTH7,
       sum(gla.CR_AMOUNT_07) as CRMonth7,
       sum(gla.DB_AMOUNT_08) AS DBMONTH8,
       sum(gla.CR_AMOUNT_08) as CRMonth8,
       sum(gla.DB_AMOUNT_09) AS DBMONTH9,
       sum(gla.CR_AMOUNT_09) as CRMonth9,
       sum(gla.DB_AMOUNT_10) AS DBMONTH10,
       sum(gla.CR_AMOUNT_10) as CRMonth10,
       sum(gla.DB_AMOUNT_11) AS DBMONTH11,
       sum(gla.CR_AMOUNT_11) as CRMonth11,
       sum(gla.DB_AMOUNT_12) AS DBMONTH12,
       sum(gla.CR_AMOUNT_12) as CRMonth12

from GLAMOUNTS gla

join GLMASTER glm
on gla.ACCOUNT = glm.ACCOUNT
and gla.ACCT_UNIT = glm.ACCT_UNIT
and gla.SUB_ACCOUNT = glm.SUB_ACCOUNT
and gla.COMPANY = glm.COMPANY

join GLCHARTDTL glc
ON glm.CHART_NAME = glc.CHART_NAME
AND glm.ACCOUNT = glc.ACCOUNT
and glm.SUB_ACCOUNT = glc.SUB_ACCOUNT

JOIN GLCHARTSUM gls
on glc.CHART_NAME = gls.CHART_NAME
AND glc.SUMRY_ACCT_ID = gls.SUMRY_ACCT_ID

WHERE gla.FISCAL_YEAR = year(getdate())--2014
AND gla.ACCOUNT < 4000
and gla.COMPANY <> 101
GROUP BY gla.COMPANY, gla.ACCOUNT, gla.SUB_ACCOUNT, gla.ACCT_UNIT, glc.ACCOUNT_DESC, gls.SUMMARY_ACCT, gls.ACCOUNT_DESC, gla.MAINT_DATE

HAVING (Sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01) +
     sum(gla.DB_AMOUNT_02) + sum(gla.CR_AMOUNT_02) + sum(gla.DB_AMOUNT_03) + sum(gla.CR_AMOUNT_03) + sum(gla.DB_AMOUNT_04) + sum(gla.CR_AMOUNT_04) +
     sum(gla.DB_AMOUNT_05) + sum(gla.CR_AMOUNT_05) + sum(gla.DB_AMOUNT_06) + sum(gla.CR_AMOUNT_06)) <>  0 --aor sum(gla.CR_AMOUNT_06 <> 0  
     --sum(gla.DB_AMOUNT_06) <>0
0
Comment
Question by:metalteck
[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
  • 4
  • 2
7 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 40411434
Are you sure you getting the error? I did a syntax check and it came back clean - obviously not being able to execute it as I don't have your DB/tables.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40411451
>but keep on getting an error.  "Incorrect Syntax near '<'.
Double-click on the above line in SSMS and the cursor will jump to where the error occured.
Then type the location in this question.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40411456
sum(gla.DB_AMOUNT_05) + sum(gla.CR_AMOUNT_05) + sum(gla.DB_AMOUNT_06) + sum(gla.CR_AMOUNT_06)) <>  0 --aor sum(gla.CR_AMOUNT_06 <> 0  

If the above is actually two lines then you are missing either a comma or an expression like +
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:metalteck
ID: 40411522
Sorry guys, the last line of the having clause was commented out.
The last line is actually 2 conditions

here is the correct Having statement:

HAVING (Sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01) +
     sum(gla.DB_AMOUNT_02) + sum(gla.CR_AMOUNT_02) + sum(gla.DB_AMOUNT_03) + sum(gla.CR_AMOUNT_03) + sum(gla.DB_AMOUNT_04) + sum(gla.CR_AMOUNT_04) +
     sum(gla.DB_AMOUNT_05) + sum(gla.CR_AMOUNT_05) + sum(gla.DB_AMOUNT_06) + sum(gla.CR_AMOUNT_06)) <>  0  and sum(gla.CR_AMOUNT_06 <> 0
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40411559
Based on the above, missing the final ) at the very end of HAVING. Indenting would make it easier to read.
HAVING (
   Sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + 
   sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01) + 
   sum(gla.DB_AMOUNT_02) + sum(gla.CR_AMOUNT_02) + 
   sum(gla.DB_AMOUNT_03) + sum(gla.CR_AMOUNT_03) + 
   sum(gla.DB_AMOUNT_04) + sum(gla.CR_AMOUNT_04) +
   sum(gla.DB_AMOUNT_05) + sum(gla.CR_AMOUNT_05) + 
   sum(gla.DB_AMOUNT_06) + sum(gla.CR_AMOUNT_06)) <>  0  
   and sum(gla.CR_AMOUNT_06 <> 0) -- <-- here

Open in new window

btw I don't see any NULL handling such as ISNULL or COALESCE anywhere, so make sure that a single NULL doesn't blow up the entire HAVING expression.
0
 

Author Comment

by:metalteck
ID: 40411583
Thats it. I was missing the last ).

How would I check for nulls?
How would I use coalesce?

Is there a better way to sum each table other than listing them individually?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40411602
>Is there a better way to sum each table other than listing them individually?
I think using HAVING will be as optimal as any other way, such as a subquery, but I'll step back from that point to encourage other experts to respond

SELECT ISNULL(any number, 0) will return any number if it's not NULL, and 0 if NULL.
SELECT COALESE(any number, this number too, or that number, 0) is same as above except you can tell T-SQL to pick the first non-NULL value from more than two values.
0

Featured Post

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

623 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