Solved

Multiple Aggregate Functions in the Having Clause

Posted on 2014-10-29
7
73 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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:metalteck
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 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

10 Experts available now in Live!

Get 1:1 Help Now