Solved

Multiple Aggregate Functions in the Having Clause

Posted on 2014-10-29
7
75 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
  • 4
  • 2
7 Comments
 
LVL 39

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

864 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

19 Experts available now in Live!

Get 1:1 Help Now