Solved

Multiple Aggregate Functions in the Having Clause

Posted on 2014-10-29
7
78 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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