DB2 SQL Group By on Year(DATE)

[schauncey]

I have the following Select Statement<br><br>Select BLACCT, Sum(BLTOTQ) as sumofBLTOTQ, Sum(BLTOTW) as sumofBLTOTW, Count(BLACCT) as countofBLACCT, BLRECN, sum(BLSLAM) as sumofBLSLAM, sum(BLPAID) as sumofBLPAID, <br>YEAR(BLSHYY) From ttcdd.EDIBOLDB Where (Year(BLSHYY) &gt;= 2000 and Year(BLSHYY) &lt;= 2000) and BLACCT = UCase('xxxx') Group by BLACCT, BLRECN, BLSHYY Order By BLRECN<br><br><br>I want the Group By to Group at the Year level - i.e. Sum all entries in the same Year and return 1 line...If I put YEAR(BLSHYY) in Group By I get an error...How do I do this?<br>

 

[Guest_imported]

You must include YEAR(BLSHYY) in your SELECT statement if you want to use it in the GROUP BY clause.<br><br>Currently, your query will only return one year, 2000<br><br>Also, you could optimise your query by simply saying Year(BLSHYY) = 2000 instead of (Year(BLSHYY) &gt;= 2000 and Year(BLSHYY) &lt;= 2000) <br><br>Also, replace the Ucase('xxxx') with 'XXXX'.&nbsp;&nbsp;This will remove an unneccesary step for the SQL engine