Solved

Multiple Aggregate Functions in the Having Clause

Posted on 2014-10-29
7
80 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 65

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 65

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 65

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 65

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

738 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